
package com.compuvest.advert;

// <editor-fold defaultstate="collapsed" desc="import">
import com.compuvest.mail.GoogleEmail;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.MessageFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import org.apache.commons.net.ftp.FTPClient;
import org.apache.commons.net.ftp.FTPConnectionClosedException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
// </editor-fold>

public class AdvertFile extends HttpServlet {

// <editor-fold defaultstate="collapsed" desc="init variables">
    public static Logger log = LoggerFactory.getLogger(AdvertFile.class);

    public static String PATH =	null;
    private static String SITE_URL = null;
    private static String SITE_DESC = null;
    private static String IMG_URL = null;
    private static String BIG_IMG_URL = null;

    private SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd");
    private String currentTime;

    private File dir;

    private DataSource ds;
    private GoogleEmail gmail;
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="init()">

    @Override
    public void init(ServletConfig config) throws ServletException {
        super.init(config);

        try {
            Context initCtx = new InitialContext();
            ds = (DataSource)initCtx.lookup("jdbc/mysql");
        }
        catch(Exception e) {
            log.error("init(): " + e);
        }

        // init google email
        gmail = new GoogleEmail();

        // set PATH
        SITE_URL = config.getInitParameter("SITE_URL");
        if(SITE_URL == null) {
            log.error("SITE_URL is null define in web.xml");
            SITE_URL = "HTTP://URL_LINK_NOT_DEFINED/";
        }
        SITE_DESC = config.getInitParameter("SITE_DESC");
        if(SITE_DESC == null) {
            log.error("SITE_DESC is null define in web.xml");
            SITE_DESC = "HTTP://URL_DESC_NOT_DEFINED/";
        }
        IMG_URL = config.getInitParameter("IMAGE_URL");
        if(IMG_URL == null) {
            log.error("IMG_URL is null define in web.xml");
            IMG_URL = "HTTP://IMAGE_LINK_NOT_DEFINED/";
        }
        BIG_IMG_URL = config.getInitParameter("BIG_IMAGE_URL");
        if(BIG_IMG_URL == null) {
            log.error("BIG_IMG_URL is null define in web.xml");
            BIG_IMG_URL = "HTTP://BIG_IMAGE_LINK_NOT_DEFINED/";
        }
        PATH = config.getInitParameter("ADVERT_PATH");
        if(PATH == null) {
            log.error("ADVERT_PATH is null define in web.xml");
            PATH = "/tmp";
        }

        dir = new File(PATH);
        // check saveDirectory is truely a directory
        if(!dir.isDirectory()) {
            log.error(PATH + " not directory");
        }
        // check saveDirectory is writable
        if(!dir.canWrite()) {
            log.error(PATH + " not writable");
        }

        log.info("init()");
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="processRequest()">

    protected void processRequest(HttpServletRequest request, HttpServletResponse response)  throws ServletException, IOException {

        response.setContentType("text/xml;charset=UTF-8");
        String advert = request.getParameter("advert");
        if(advert != null) {
            try (PrintWriter out = response.getWriter()) {
                String result = doAdvertFile(advert);

                StringBuilder xml = new StringBuilder();
                xml.append("<?xml version='1.0' encoding='UTF-8'?>");
                xml.append("<status>");
                xml.append("<sessionid>").append(request.getSession().getId()).append("</sessionid>");
                xml.append("<message>").append(result).append("</message>");
                xml.append("</status>");

                out.print(xml.toString());
            }
        }
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="doAdvertFile()">

    protected String doAdvertFile(String site) {

        log.debug("-=START=-");

        currentTime = df.format(new Date());

	StringBuilder body = new StringBuilder(500);
        body.append("AdvertFile Total memory: ").append(Runtime.getRuntime().totalMemory());
        body.append("\nFree  memory: ").append(Runtime.getRuntime().freeMemory());
        body.append("\n\nAdvertFile start: ").append(new Date()).append('\n');

        initTempItemsTable();

        String result;
        long start;
        List<Advert> advertList = getAdverts();
        for(Advert advert : advertList) {

            if("grabber".equals(advert.getName()) && "all".equals(site)) {
                start = System.currentTimeMillis();
                result = makePriceGrabber(advert);
                body.append("\nPriceGrabber=\t").append(result).append("\t(").append(System.currentTimeMillis() - start).append("ms)");
            }
            else if("cj".equals(advert.getName()) && "all".equals(site)) {
                start = System.currentTimeMillis();
                result = makeCJ(advert);
                body.append("\nCJ=\t\t").append(result).append("\t(").append(System.currentTimeMillis() - start).append("ms)");
            }
            else if("links".equals(advert.getName()) && "all".equals(site)) {
                start = System.currentTimeMillis();
                result = makeLinks(advert);
                body.append("\nLinks=\t\t").append(result).append("\t(").append(System.currentTimeMillis() - start).append("ms)");
            }
            else if("shopping".equals(advert.getName()) && "all".equals(site)) {
                start = System.currentTimeMillis();
                result = makeShopping(advert);
                body.append("\nShopping=\t").append(result).append("\t(").append(System.currentTimeMillis() - start).append("ms)");
            }
            else if("brokerscreen".equals(advert.getName()) && "all".equals(site)) {
                start = System.currentTimeMillis();
                result = makeBrokerScreen(advert);
                body.append("\nBrokerScreen=\t").append(result).append("\t(").append(System.currentTimeMillis() - start).append("ms)");
            }
            // this advert for Xan not bizrate
            else if("bizrate".equals(advert.getName()) && "all".equals(site)) {
                start = System.currentTimeMillis();
                result = makeBizrate(advert);
                body.append("\nBizRate=\t").append(result).append("\t(").append(System.currentTimeMillis() - start).append("ms)");
            }
            else if("nexttag".equals(advert.getName()) && "all".equals(site)) {
                start = System.currentTimeMillis();
                result = makeNexttag(advert);
                body.append("\nNextTag=\t").append(result).append("\t(").append(System.currentTimeMillis() - start).append("ms)");
            }
            else if("brokerbin".equals(advert.getName()) && "all".equals(site)) {
                start = System.currentTimeMillis();
                result = makeBrokerBin(advert);
                body.append("\nBrokerBin=\t").append(result).append("\t(").append(System.currentTimeMillis() - start).append("ms)");
            }
            else if("buyedge".equals(advert.getName()) && "all".equals(site)) {
                start = System.currentTimeMillis();
                result = makeBuyersEdge(advert);
                body.append("\nBuyersEdge=\t").append(result).append("\t(").append(System.currentTimeMillis() - start).append("ms)");
            }
            else if("cnet".equals(advert.getName()) && "all".equals(site)) {
                start = System.currentTimeMillis();
                result = makeCNET(advert);
                body.append("\nCNET=\t\t").append(result).append("\t(").append(System.currentTimeMillis() - start).append("ms)");
            }
            else if("bing".equals(advert.getName()) && "bing".equals(site)) {
                start = System.currentTimeMillis();
                result = makeBing(advert);
                body.append("\nBing=\t\t").append(result).append("\t(").append(System.currentTimeMillis() - start).append("ms)");
            }

	}

        // make file for PriceFalls
        if("pricefalls".equals(site)) {
            start = System.currentTimeMillis();
            result = makePriceFallsCVS();
            body.append("\nPriceFallsCVS=\t\t").append(result).append("\t(").append(System.currentTimeMillis() - start).append("ms)\n");

            start = System.currentTimeMillis();
            result = makePriceFallsALL();
            body.append("\nPriceFallsALL=\t\t").append(result).append("\t(").append(System.currentTimeMillis() - start).append("ms)\n");
        }

        // make file for Apex
        if("apex".equals(site)) {
            start = System.currentTimeMillis();
            result = makeApex();
            body.append("\nApex=\t\t").append(result).append("\t(").append(System.currentTimeMillis() - start).append("ms)\n");
        }

        // make file for Epy
        if("epy".equals(site)) {
            result = makeEpy();
            body.append("\nEpy:").append(result);
        }


        if("all".equals(site)) {
            String resStr = sendPowerSourceFTPFile();
            body.append("\nsend to PowerSourceOnline: ").append(resStr).append('\n');

            result = makeOneWay();
            body.append("\nOneWay:").append(result);

        }

        body.append("\nAdvertFile finish: ").append(new Date()).append("\n");

        sendEmail(body.toString());

        log.debug("-=FINISH=-\n" + body.toString());

        return body.toString();
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="initTempItemsTable()">

    final static String MAKE_TABLE_SORT =
	"CREATE TABLE IF NOT EXISTS advert.tempItemsSortForAdvert (" +
	"item_id int(10) unsigned NOT NULL default '0'," +
	"mfrNumber varchar(30) NOT NULL default ''," +
	"conditions int(1) unsigned NOT NULL default '0'," +
	"vendorID tinyint(3) NOT NULL default '2'," +
	"KEY idx_item_id(item_id)," +
	"KEY idx_mfrNumber(mfrNumber)," +
	"KEY idx_conditions(conditions)," +
	"KEY idx_vendorID(vendorID)" +
	") ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='sort by sell_price,conditions temporary table for advert'";

    final static String MAKE_TABLE_GROUP =
	"CREATE TABLE IF NOT EXISTS advert.tempItemsGroupForAdvert (" +
	"item_id int(10) unsigned NOT NULL default '0'," +
	"mfrNumber varchar(30) NOT NULL default ''," +
	"conditions varchar(20) NOT NULL default ''," +
	"KEY idx_item_id(item_id)" +
	") ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='finish temporary table for advert'";

    final static String INIT_ITEMS_SORT_TABLE =
	"INSERT INTO advert.tempItemsSortForAdvert " +
        "(SELECT it.item_id item_id,ad.mfrNumber mfrNumber,LEFT(RIGHT(it.accwareCatID,2),1) conditions,it.vendor_id vendorID " +
        "FROM sklad.items it " +
	"LEFT JOIN sklad.itemsAdvert ad USING(item_id) " +
        "WHERE it.active='Y' AND ad.mfrNumber<>'' " +
	"ORDER BY it.sell_price)";

    final static String INIT_ITEMS_GROUP_TABLE =
	"INSERT INTO advert.tempItemsGroupForAdvert " +
        "(SELECT isa.item_id item_id,isa.mfrNumber mfrNumber,cs.description conditions " +
        "FROM advert.tempItemsSortForAdvert isa " +
        "LEFT JOIN sklad.conditions cs ON isa.conditions=cs.id " +
        "GROUP BY isa.mfrNumber,isa.conditions)";

    private void initTempItemsTable() {

        log.debug("initTempItemsTable");

        try(Connection cn = ds.getConnection()) {

            PreparedStatement ps = cn.prepareStatement(MAKE_TABLE_SORT);
            ps.executeUpdate();
            ps.close();

            ps = cn.prepareStatement("TRUNCATE advert.tempItemsSortForAdvert");
            ps.executeUpdate();
            ps.close();

            ps = cn.prepareStatement(MAKE_TABLE_GROUP);
            ps.executeUpdate();
            ps.close();

            ps = cn.prepareStatement("TRUNCATE advert.tempItemsGroupForAdvert");
            ps.executeUpdate();
            ps.close();

            ps = cn.prepareStatement(INIT_ITEMS_SORT_TABLE);
            ps.executeUpdate();
            ps.close();

            ps = cn.prepareStatement(INIT_ITEMS_GROUP_TABLE);
            ps.executeUpdate();
            ps.close();
        }
        catch(SQLException e) {
            log.error("initTempItemsTable(): " + e);
        }
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getAdverts()">

    final static String GETADVERTS =
	"SELECT advert_id,name,fileName,delim " +
        "FROM sklad.advert " +
        "WHERE active=TRUE AND fileName IS NOT NULL";

    private List<Advert> getAdverts() {

        log.debug("getAdverts");

        List<Advert> advertList = new ArrayList<>();
        try(Connection cn = ds.getConnection()) {

            PreparedStatement ps = cn.prepareStatement(GETADVERTS);
            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                advertList.add(new Advert(
                        rs.getInt("advert_id"),
                        rs.getString("name"),
                        rs.getString("fileName"),
                        rs.getString("delim") ));
            }
            rs.close();
            ps.close();
        }
        catch(SQLException e) {
            log.error("getAdverts(): " + e);
        }

        return advertList;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getVendorTable(int groupid)">
/*
SELECT subcat_id,GROUP_CONCAT(vendor_id) vendors
FROM advert.advertSend where advert_id=18
GROUP BY subcat_id;
 */
    final static String GETVENDORS =
	"SELECT subcat_id,GROUP_CONCAT(vendor_id) vendors " +
	"FROM advert.advertSend where advert_id=? " +
        "GROUP BY subcat_id";

    private Map<Integer,String> getVendorTable(int groupid) {

        Map<Integer,String> vendorTable = new HashMap<>();
        try(Connection cn = ds.getConnection()) {

            PreparedStatement ps = cn.prepareStatement(GETVENDORS);
            ps.setInt(1, groupid);
            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                vendorTable.put(rs.getInt("subcat_id"), '(' + rs.getString("vendors") + ')');
            }
            rs.close();
            ps.close();
        }
        catch(SQLException e) {
            log.error("getVendorTable(): " + e);
        }

        return vendorTable;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="makePriceGrabber()">
// CVS=2, CAS=83, ADV=27, AAA=29

    final static String SELECT_PRICEGGRABER =
        "SELECT it.item_id itemID,it.accwareCatID sku,ta.mfrNumber mfrNumber,ad.upc upc,ad.mfrName mfrName,it.name itemName,it.accwareCatID imageName," +
        "LEFT(RIGHT(it.accwareCatID,2),2) accwareCode,ta.conditions conditions,it.sell_price sell_price,it.weight weight,IF(cad.grabber='','Generic',cad.grabber) catName " +
        "FROM advert.tempItemsGroupForAdvert ta " +
        "LEFT JOIN sklad.items it USING(item_id) " +
        "LEFT JOIN sklad.itemsAdvert ad USING(item_id)  " +
        "LEFT JOIN sklad.categoryAdvert cad ON it.category_id=cad.category_id " +
        "WHERE it.active='Y' AND it.category_id=? AND it.sell_price > 20 AND it.vendor_id IN ";

    final static String HEADER_PRICEGGRABER =
        // 1    2         3              4            5-6            7-1-8-9-6                10                11                12             13           14     15       16
        "MPN{0}UPC{0}Manufacturer{0}Product Name{0}Image URLs{0}Referring Product URL{0}Product Condition{0}Availability{0}Selling Price{0}Shipping costs{0}Weight{0}SKU{0}Grabber categories\r\n";

    final static String RESULT_PRICEGGRABER =
        "{1}{0}{2}{0}{3}{0}{4}{0}{5}{6}.jpg{0}{7}{1}&advsite={8}&dtm={9}&sku={6}{0}{10}{0}{11}{0}{12,number,#.00}{0}{13,number,#.00}{0}{14,number,#.00}{0}{15}{0}{16}\r\n";

    private String makePriceGrabber(Advert advert) {

        log.debug("makePriceGrabber");

        Map<Integer,String> vendorTable = getVendorTable(advert.getAdvertID());
        if(vendorTable.isEmpty()) { return "no Vendor Find"; }

        int count = 0;
        try(Connection cn = ds.getConnection()) {
            String condition;

            PreparedStatement ps;
            ResultSet rs;

            BufferedWriter of = new BufferedWriter(new FileWriter(PATH + advert.getFileName(), false));
            of.write(MessageFormat.format(HEADER_PRICEGGRABER, advert.getDelim()));

            for(int subcatid : vendorTable.keySet()) {

                ps = cn.prepareStatement(SELECT_PRICEGGRABER + vendorTable.get(subcatid));
                ps.setInt(1, subcatid);
                rs = ps.executeQuery();
                while(rs.next()) {
                    count++;

                    switch(rs.getInt("accwareCode")) {
                        case 0:
                        case 1:
                        case 5: condition = "New"; break;
                        case 3:
                        case 9:
                        case 59: condition = "Open Box"; break;
                        case 10:
                        case 11:
                        case 12:
                        case 13:
                        case 31:
                        case 32: condition = "Refurbished"; break;
                        case 22:
                        case 41:
                        case 42:
                        case 48: condition = "Used"; break;
                        case 71:
                        case 72:
                        case 81:
                        case 82:
                        case 91:
                        case 92: condition = "Like New"; break;
                        default: condition = "OEM";
                    }

                    of.write(
                    MessageFormat.format(RESULT_PRICEGGRABER, new Object[] {
/* 0*/               advert.getDelim(),
/* 1*/               rs.getString("mfrNumber"),
/* 2*/               "",//rs.getString("upc"),
/* 3*/               rs.getString("mfrName"),
/* 4*/               rs.getString("itemName"),
/* 5*/               IMG_URL,
/* 6*/               rs.getString("imageName"),
///* 7*/               SITE_DESC,
/* 7*/               SITE_URL,
/* 8*/               advert.getName(),
/* 9*/               currentTime,
/*10*/               condition,
/*11*/               "Yes",
/*12*/               rs.getDouble("sell_price"),
/*13*/               8d + rs.getDouble("weight") * 0.5,
/*14*/               rs.getDouble("weight"),
/*15*/               rs.getString("sku"),
/*16*/               rs.getString("catName")
                    }));
                }
                ps.close();
                rs.close();
            }
            of.close();
        }
        catch(IOException | SQLException e) {
            log.error("makePriceGrabber(): " + e);
        }

        return "" + count;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="makeOneWay()">
// CVS=2

    final static String SELECT_ONEWAY =
        "SELECT ta.mfrNumber mfrNumber,ad.upc upc,ad.mfrName mfrName,it.name itemName,sc.name catName,it.accwareCatID imageName," +
        "LEFT(RIGHT(it.accwareCatID,2),2) accwareCode,ta.conditions conditions,it.sell_price sell_price,it.weight weight " +
        "FROM advert.tempItemsGroupForAdvert ta " +
        "LEFT JOIN sklad.items it USING(item_id) " +
        "LEFT JOIN sklad.itemsAdvert ad USING(item_id)  " +
    	"LEFT JOIN sklad.category sc USING(category_id) " +
        "WHERE it.active='Y' AND it.vendor_id=2 LIMIT 1000";

    final static String HEADER_ONEWAY =
        // 1    2         3              4            5-6            7-1-8-9-6                10              11           12              13             14            15
        "MPN{0}UPC{0}Manufacturer{0}Product Name{0}Image URLs{0}Referring Product URL{0}Product Condition{0}Category{0}Availability{0}Selling Price{0}Shipping costs{0}Weight\r\n";

    final static String RESULT_ONEWAY =
        "{1}{0}{2}{0}{3}{0}{4}{0}{5}{6}.jpg{0}{7}{1}&advsite={8}&dtm={9}&sku={6}{0}{10}{0}{11}{0}{12}{0}{13,number,#.00}{0}{14,number,#.00}{0}{15,number,#.00}\r\n";

    private String makeOneWay() {

        log.debug("makeOneWay");

        int count = 0;
        try(Connection cn = ds.getConnection()) {
            String condition;

            PreparedStatement ps;
            ResultSet rs;

            BufferedWriter of = new BufferedWriter(new FileWriter(PATH + "oneway.txt", false));
            of.write(MessageFormat.format(HEADER_ONEWAY, ","));


            ps = cn.prepareStatement(SELECT_ONEWAY);
            rs = ps.executeQuery();
            while(rs.next()) {
                count++;

                switch(rs.getInt("accwareCode")) {
                    case 0:
                    case 1:
                    case 5: condition = "New"; break;
                    case 3:
                    case 9:
                    case 59: condition = "Open Box"; break;
                    case 10:
                    case 11:
                    case 12:
                    case 13:
                    case 31:
                    case 32: condition = "Refurbished"; break;
                    case 22:
                    case 41:
                    case 42:
                    case 48: condition = "Used"; break;
                    case 71:
                    case 72:
                    case 81:
                    case 82:
                    case 91:
                    case 92: condition = "Like New"; break;
                    default: condition = "OEM";
                }

                of.write(
                MessageFormat.format(RESULT_ONEWAY, new Object[] {
/* 0*/               ",",
/* 1*/               rs.getString("mfrNumber"),
/* 2*/               "",//rs.getString("upc"),
/* 3*/               rs.getString("mfrName"),
/* 4*/               rs.getString("itemName"),
/* 5*/               IMG_URL,
/* 6*/               rs.getString("imageName"),
///* 7*/               SITE_DESC,
/* 7*/               SITE_URL,
/* 8*/               "oneway",
/* 9*/               currentTime,
/*10*/               condition,
/*11*/               rs.getString("catName"),
/*12*/               "Yes",
/*13*/               rs.getDouble("sell_price"),
/*14*/               8d + rs.getDouble("weight") * 0.5,
/*15*/               rs.getDouble("weight")
                    }));
                }
                ps.close();
                rs.close();
            of.close();
        }
        catch(IOException | SQLException e) {
            log.error("makeOneWay(): " + e);
        }

        log.debug("send to ftp OneWay");
        String result = sendOneWayFTPFile();

        return count + "\nsend OneWay file to ftp:\n" + result;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="makeCJ()">

    final static String SELECT_CJ =
        "SELECT it.accwareCatID accwareCode,ad.mfrName mfrName,ta.mfrNumber mfrNumber,it.sell_price sell_price," +
        "it.name itemName,sc.name catName,ta.conditions conditions,LEFT(RIGHT(it.accwareCatID,2),1) condID,it.weight weight,ad.upc upc " +
        "FROM advert.tempItemsGroupForAdvert ta " +
        "LEFT JOIN sklad.items it USING(item_id) " +
        "LEFT JOIN sklad.itemsAdvert ad USING(item_id)  " +
    	"LEFT JOIN sklad.category sc USING(category_id) " +
        "WHERE it.active='Y' AND it.category_id=? AND it.vendor_id IN ";

    final static String HEADER_CJ =
        "&CID=2319977\n"+
        "&SUBID=17667\n"+
        "&DATEFMT=MM/DD/YYYY HH12:MI PM\n"+
        "&PROCESSTYPE=OVERWRITE\n"+
        "&AID=10547627\n"+
        //            1        2           1          3  4-5-6-7-3       8         9-3       10       11       12         13                   5                14         15             16
        "&PARAMETERS=NAME{0}KEYWORDS{0}DESCRIPTION{0}SKU{0}BUYURL{0}AVAILABLE{0}IMAGEURL{0}PRICE{0}CURRENCY{0}UPC{0}ADVERTISERCATEGORY{0}MANUFACTURER{0}MANUFACTURERID{0}CONDITION{0}STANDARDSHIPPINGCOST\r\n";

    final static String RESULT_CJ =
        "{1}{0}{2}{0}{1}{0}{3}{0}{4}{5}&advsite={6}&dtm={7}&sku={3}{0}{8}{0}{9}{3}.jpg{0}{10,number,#.00}{0}{11}{0}{12}{0}{13}{0}{14}{0}{5}{0}{15}{0}{16}\r\n";

    private String makeCJ(Advert advert) {

        Map<Integer,String> vendorTable = getVendorTable(advert.getAdvertID());
        if(vendorTable.isEmpty()) { return "no Vendor Find"; }

        int count = 0;

        try(Connection cn = ds.getConnection()) {
            String condition;

            PreparedStatement ps;
	    ResultSet rs;

            BufferedWriter ofnew = new BufferedWriter(new FileWriter(PATH + advert.getFileName(), false));
            ofnew.write(MessageFormat.format(HEADER_CJ, advert.getDelim()));

            for(int subcatid : vendorTable.keySet()) {

                ps = cn.prepareStatement(SELECT_CJ + vendorTable.get(subcatid));
                ps.setInt(1, subcatid);
                rs = ps.executeQuery();
                while (rs.next()) {
                    count++;

                    switch(rs.getInt("condID")) {
                        // New,New Pulls/OEM
                        case 0:
                        case 8: condition = "NEW"; break;
                        // Mfr.Refurbished,Refurbished,Reconditioned
                        case 1:
                        case 3:
                        case 7: condition = "REFURBISHED"; break;
                        // Defective, Used, ***,Extra Life,Like New
                        case 2:
                        case 4:
                        case 5:
                        case 6:
                        case 9: condition = "USED"; break;
                        default: condition = "NEW";
                    }

                    ofnew.write(
                    MessageFormat.format(RESULT_CJ, new Object[] {
/* 0*/               advert.getDelim(),
/* 1*/               rs.getString("itemName"),
/* 2*/               rs.getString("itemName"), //rs.getString("catName"),
/* 3*/               rs.getString("accwareCode"),
/* 4*/               SITE_URL,
/* 5*/               rs.getString("mfrNumber"),
/* 6*/               "cj",
/* 7*/               currentTime,
/* 8*/               "YES",
/* 9*/               IMG_URL,
/*10*/               rs.getDouble("sell_price"),
/*11*/               "USD",
/*12*/               rs.getString("upc"),
/*13*/               rs.getString("catName"),
/*14*/               rs.getString("mfrName"),
/*15*/               condition,
/*16*/               "0.00"
                    }));
                }
                ps.close();
                rs.close();
            }
            ofnew.close();
        }
        catch(IOException | SQLException e) {
            log.error("makeCJ(): " + e);
        }

        return "" + count;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="makeLinks()">

    final static String SELECT_LINKS =
        "SELECT it.accwareCatID accwareCode,LEFT(it.accwareCatID,9) accwareID,ad.mfrName mfrName," +
        "it.sell_price sell_price,it.name itemName,sc.name catName,ta.mfrNumber mfrNumber " +
        "FROM advert.tempItemsGroupForAdvert ta " +
        "LEFT JOIN sklad.items it USING(item_id) " +
        "LEFT JOIN sklad.itemsAdvert ad USING(item_id)  " +
    	"LEFT JOIN sklad.category sc USING(category_id) " +
        "WHERE it.active='Y' AND it.category_id=? AND it.vendor_id IN ";

    final static String HEADER_LINKS =
        //    18           2             3               4                  19                 5-6-7-8-1        9-1               19
        "Product ID{0}Product Name{0}Sku Number{0}Primary Category{0}Secondary Category(ies){0}Product URL{0}Product Image URL{0}Buy URL{0}" +
        //        10                            19                 19          19             11           19
        "Short Product Description{0}Long Product Description{0}Discount{0}Discount Type{0}Sale Price{0}Retail Price{0}" +
        //    19         19       19        19          12              19            13                 6                   20
        "Begin Date{0}End Date{0}Brand{0}Shipping{0}Is Deleted Flag{0}Keyword(s){0}Is All Flag{0}Manufacturer Part #{0}Manufacturer Name{0}" +
        //       19                 19                   19               19               14                    15
        "Shipping Information{0}Availability{0}Universal Pricing Code{0}Class ID{0}Is Product Link Flag{0}Is Storefront Flag{0}" +
        //         16              17      19
        "Is Merchandiser Flag{0}Currency{0}M1\r\n";

    final static String RESULT_LINKS =
        "{18}{0}{2}{0}{3}{0}{4}{0}{19}{0}{5}{6}&advsite={7}&dtm={8}&sku={1}{0}{9}{1}.jpg{0}{19}{0}" +
        "{10}{0}{19}{0}{19}{0}{19}{0}{11,number,#.00}{0}{19}{0}" +
        "{19}{0}{19}{0}{19}{0}{19}{0}{12}{0}{19}{0}{13}{0}{6}{0}{20}{0}" +
        "{19}{0}{19}{0}{19}{0}{19}{0}{14}{0}{15}{0}" +
        "{16}{0}{17}{0}{19}\r\n";

    private String makeLinks(Advert advert) {

        Map<Integer,String> vendorTable = getVendorTable(advert.getAdvertID());
        if(vendorTable.isEmpty()) { return "no Vendor Find"; }

        int count = 0;

        try(Connection cn = ds.getConnection()) {

            PreparedStatement ps;
	    ResultSet rs;

            BufferedWriter ofnew = new BufferedWriter(new FileWriter(PATH + advert.getFileName(), false));
            ofnew.write(MessageFormat.format(HEADER_LINKS, advert.getDelim()));

            for(int subcatid : vendorTable.keySet()) {

                ps = cn.prepareStatement(SELECT_LINKS + vendorTable.get(subcatid));
                ps.setInt(1, subcatid);
                rs = ps.executeQuery();
                while (rs.next()) {
                    count++;

                    ofnew.write(
                    MessageFormat.format(RESULT_LINKS, new Object[] {
/* 0*/               advert.getDelim(),
/* 1*/               rs.getString("accwareCode"),
/* 2*/               rs.getString("itemName"),
/* 3*/               rs.getString("accwareCode"),
/* 4*/               rs.getString("catName"),
/* 5*/               SITE_URL,
/* 6*/               rs.getString("mfrNumber"),
/* 7*/               "links",
/* 8*/               currentTime,
/* 9*/               IMG_URL,
/*10*/               rs.getString("itemName"),
/*11*/               rs.getDouble("sell_price"),
/*12*/               "N",
/*13*/               "Y",
/*14*/               "N",
/*15*/               "N",
/*16*/               "N",
/*17*/               "USD",
/*18*/               rs.getString("accwareID"),
/*19*/               "",
/*20*/               rs.getString("mfrName")
                    }));
                }
                ps.close();
                rs.close();
            }
            ofnew.close();
        }
        catch(IOException | SQLException e) {
            log.error("makeLinks(): " + e);
        }

        log.debug("send LinksShare file to ftp");
        String result = "Error connection";//sendLinksShareFTPFile();

        return count + "\nsend LinksShare file to ftp:\n" + result;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="makeBing()">
// aka jellyfish

    final static String SELECT_BING =
        "SELECT it.accwareCatID accwareCode,ad.mfrName mfrName,ta.mfrNumber mfrNumber,it.sell_price sell_price," +
        "it.name itemName,sc.name catName,ta.conditions conditions,cs.froogle cond,it.weight weight,ad.upc upc " +
        "FROM advert.tempItemsGroupForAdvert ta " +
        "LEFT JOIN sklad.items it USING(item_id) " +
        "LEFT JOIN sklad.itemsAdvert ad USING(item_id)  " +
    	"LEFT JOIN sklad.category sc USING(category_id) " +
    	"LEFT JOIN sklad.conditions cs ON cs.id=LEFT(RIGHT(it.accwareCatID,2),1) " +
        "WHERE it.active='Y' AND it.category_id=? AND it.vendor_id IN ";

    final static String HEADER_BING =
        //          1              2        3              4            5     6      7      1    8-5-9-10-1     11        12          3            13-1          14             15            16              17           18
        "Merchant Product ID{0}Reserved{0}Title{0}BrandorManufacturer{0}MPN{0}UPC{0}ISBN{0}SKU{0}Product URL{0}Price{0}In Stock{0}Description{0}Image URL{0}Shipping Costs{0}Merchants Category{0}Shipping Weight{0}Commission{0}Condition\r\n";

    final static String RESULT_BING =
        "{1}{0}{2}{0}{19}{0}{4}{0}{5}{0}{6}{0}{7}{0}{1}{0}{8}{5}&advsite={9}&dtm={10}&sku={1}{0}{11,number,#.00}{0}{12}{0}{3}{0}{13}{1}.jpg{0}{14}{0}{15}{0}{16,number,0.00}{0}{17}{0}{18}\r\n";

    private String makeBing(Advert advert) {

        log.debug("makeBing");

        Map<Integer,String> vendorTable = getVendorTable(advert.getAdvertID());
        if(vendorTable.isEmpty()) { return "no Vendor Find"; }

        int count = 0;

        try(Connection cn = ds.getConnection()) {

            PreparedStatement ps;
	    ResultSet rs;

            BufferedWriter of = new BufferedWriter(new FileWriter(PATH + advert.getFileName(), false));
            of.write(MessageFormat.format(HEADER_BING, advert.getDelim()));

            for(int subcatid : vendorTable.keySet()) {

                ps = cn.prepareStatement(SELECT_BING + vendorTable.get(subcatid));
                ps.setInt(1, subcatid);
                rs = ps.executeQuery();
                while (rs.next()) {
                    count++;

                    of.write(
                    MessageFormat.format(RESULT_BING, new Object[] {
/* 0*/               advert.getDelim(),
/* 1*/               rs.getString("accwareCode"),
/* 2*/               "",
/* 3*/               rs.getString("itemName"),
/* 4*/               rs.getString("mfrName"),
/* 5*/               rs.getString("mfrNumber"),
/* 6*/               "",//rs.getString("upc"),
/* 7*/               "",
/* 8*/               SITE_URL,
/* 9*/               "bing",
/*10*/               currentTime,
/*11*/               rs.getDouble("sell_price"),
/*12*/               "In Stock",
/*13*/               IMG_URL,
/*14*/               "0.00", //8.0 + rs.getDouble("weight") * 0.50,
/*15*/               rs.getString("catName"),
/*16*/               rs.getDouble("weight"),
/*17*/               "2",
/*18*/               rs.getString("cond"),
/*19*/               rs.getString("catName")
                    }));
                }
                ps.close();
                rs.close();
            }
            of.close();
        }
        catch(IOException | SQLException e) {
            log.error("makeBing(): " + e);
        }

        log.debug("send Bing file to ftp");
        String result = sendBingFTPFile();

        return count + "\nsend Bing file to ftp:\n" + result;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="makeShopping(Advert advert)">
// vendor_id=2 - compuvest
//
    final static String SELECT_SHOPPING =
        "SELECT it.item_id itemID,it.accwareCatID accwareCode,ad.mfrName mfrName,ta.mfrNumber mfrNumber,it.sell_price sell_price," +
        "it.name itemName,sc.shoppingCatID categoryID,sc.name catName,ta.conditions conditions,it.weight weight,ad.upc upc " +
//        "LEFT(RIGHT(it.accwareCatID,2),2) accwareLast " +
        "FROM advert.tempItemsGroupForAdvert ta " +
        "LEFT JOIN sklad.items it USING(item_id) " +
        "LEFT JOIN sklad.itemsAdvert ad USING(item_id)  " +
    	"LEFT JOIN sklad.category sc USING(category_id) " +
        "WHERE it.active='Y' AND it.sell_price > 50 AND it.category_id=? AND ad.shopping=TRUE AND it.vendor_id IN ";
//        "AND (it.sell_price > 100 OR it.vendor_id=2) AND it.vendor_id IN ";

    final static String HEADER_SHOPPING =
        //   1           2     3           4                     5          6-7-8         9         10             11
        "Merchant SKU{0}MPN{0}UPC{0}Brand / Manufacturer{0}Product Name{0}Product URL{0}Price{0}Category ID{0}Category Name{0}" +
        //    12            13            14               15         16       17            18
        "Image URL{0}Shipping Rate{0}Shipping weight{0}Condition{0}Zip Code{0}Stock{0}Product Description\r\n";
    final static String RESULT_SHOPPING =
        "{1}{0}{2}{0}{3}{0}{4}{0}{5}{0}{6}{2}&advsite={7}&dtm={8}&sku={1}{0}{9,number,#.00}{0}{10}{0}{11}{0}{12}{1}.jpg{0}{13,number,#.00}{0}{14,number,#.00}{0}{15}{0}{16}{0}{17}{0}{18}\r\n";

    private String makeShopping(Advert advert) {

        log.debug("makeShopping");

        Map<Integer,String> vendorTable = getVendorTable(advert.getAdvertID());
        if(vendorTable.isEmpty()) { return "no Vendor Find"; }

        int count = 0;
        try(Connection cn = ds.getConnection()) {

            PreparedStatement ps;
	    ResultSet rs;

            BufferedWriter of = new BufferedWriter(new FileWriter(PATH + advert.getFileName(), false));
            of.write(MessageFormat.format(HEADER_SHOPPING, advert.getDelim()));

            for(int subcatid : vendorTable.keySet()) {

                ps = cn.prepareStatement(SELECT_SHOPPING + vendorTable.get(subcatid));
                ps.setInt(1, subcatid);
                rs = ps.executeQuery();
                while(rs.next()) {
                    count++;

                    String itemName = rs.getString("itemName").replace(',',' ');
                    String condition = "New";

                    of.write(
                    MessageFormat.format(RESULT_SHOPPING, new Object[] {
/* 0*/               advert.getDelim(),
/* 1*/               rs.getString("accwareCode"),
/* 2*/               rs.getString("mfrNumber"),
/* 3*/               rs.getString("upc"),
/* 3               (rs.getInt("accwareLast") == 0) ? rs.getString("upc") : "",*/
/* 4*/               rs.getString("mfrName"),
/* 5*/               itemName,
/* 6*/               SITE_URL,
/* 7*/               advert.getName(),
/* 8*/               currentTime,
/* 9*/               rs.getDouble("sell_price"),
/*10*/               rs.getString("categoryID"), // shopping category
/*11*/               rs.getString("catName"),
/*12*/               BIG_IMG_URL,
/*13*/               8.0 + rs.getDouble("weight") * 0.50,
/*00*///               16.0 + rs.getDouble("weight") * 0.80,
/*00*///               40.0 + rs.getDouble("weight") * 0.95,
/*14*/               rs.getDouble("weight"),
/*15*/               condition,
/*16*/               "98057",
/*17*/               "Y",
/*18*/               rs.getString("itemName")
                    }));
                }
                ps.close();
                rs.close();
            }
            of.close();
        }
        catch(IOException | SQLException e) {
            log.error("makeShopping(): " + e);
        }

        log.debug("send to ftp Shopping");
        String result = sendShoppingFTPFile();

        return count + "\nsend Shopping file to ftp:\n" + result;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="makeBrokerScreen()">

    final static String SELECT_BROKERSCREEN =
        "SELECT it.accwareCatID accwareCode,ta.mfrNumber mfrNumber," +
        "it.name itemName,it.sell_price sell_price,ta.conditions conditions " +
        "FROM advert.tempItemsGroupForAdvert ta " +
        "LEFT JOIN sklad.items it USING(item_id) " +
        "LEFT JOIN sklad.itemsAdvert ad USING(item_id)  " +
        "WHERE it.active='Y' AND ad.grabber=TRUE";

    final static String HEADER_BROKERSCREEN =
        //    1         2         3          4      5
        "part number{0}mfg{0}description{0}price{0}qty{0}cond\r\n";

    final static String RESULT_BROKERSCREEN =
        "{1}{0}{2}{0}{3}{0}{4,number,#.00}{0}{0}{5}\r\n";

    private String makeBrokerScreen(Advert advert) {

        log.debug("makeBrokerScreen");

        int count = 0;
        try(Connection cn = ds.getConnection()) {

            PreparedStatement ps;
	    ResultSet rs;

            BufferedWriter of = new BufferedWriter(new FileWriter(PATH + advert.getFileName(), false));
            of.write(MessageFormat.format(HEADER_BROKERSCREEN, advert.getDelim()));

            ps = cn.prepareStatement(SELECT_BROKERSCREEN);
            rs = ps.executeQuery();
            while(rs.next()) {
                count++;

                of.write(
                MessageFormat.format(RESULT_BROKERSCREEN, new Object[] {
/* 0*/              advert.getDelim(),
/* 1*/              rs.getString("accwareCode"),
/* 2*/              rs.getString("mfrNumber"),
/* 3*/              rs.getString("itemName").replace(',',' '),
/* 4*/              rs.getDouble("sell_price"),
/* 5*/              rs.getString("conditions")
                }));
            }
            ps.close();
            rs.close();
            of.close();
        }
        catch(IOException | SQLException e) {
            log.error("makeBrokerScreen(): " + e);
        }

        log.debug("sendEmail to BrokerScreen");
        // send email
        gmail.sendBinary(
            "webserver@compuvest.com",
            new String[] { "uploads@brokerscreen.com" },
            "Invetory from Compuvest.com " + new Date(),
            PATH + advert.getFileName()
        );

        return "" + count;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="makeBizrate(Advert advert)">

    final static String SELECT_BIZRATE =
        "SELECT ad.mfrName mfrName,ta.mfrNumber mfrNumber,it.sell_price sell_price,IF(it.stock_quantity=0,10,it.stock_quantity) qty," +
        "it.name itemName,sc.name catName,ta.conditions conditions,it.accwareCatID sku,sc.bizrateID bizID," +
        "it.weight weight,ad.upc upc " +
        "FROM advert.tempItemsGroupForAdvert ta " +
        "LEFT JOIN sklad.items it USING(item_id) " +
        "LEFT JOIN sklad.itemsAdvert ad USING(item_id)  " +
    	"LEFT JOIN sklad.category sc USING(category_id) " +
        "WHERE it.active='Y' AND it.category_id=? AND ad.bizrate=TRUE AND it.vendor_id IN ";
//     "AND (it.sell_price > 50 OR it.vendor_id IN(2,83,27,29)) AND it.vendor_id IN ";

    final static String HEADER_BIZRATE =
        // 1       2           3             4             5          6     7        8         9             10              11           12          13             14          15         16,17     18    18    18
        "SKU{0}description{0}category{0}product_type{0}expiry_date{0}qty{0}unit{0}min_order{0}price{0}payment_method{0}delivery_time{0}location{0}shipping_terms{0}repost{0}addition_info{0}pic_url{0}kw1{0}kw2{0}kw3\r\n";

    final static String RESULT_BIZRATE =
        "{1}{0}{2}{0}{3}{0}{4}{0}{5}{0}{6}{0}{7}{0}{8}{0}{9,number,#.00}{0}{10}{0}{11}{0}{12}{0}{13}{0}{14}{0}{15}{0}{16}{17}.jpg{0}{18}{0}{18}{0}{18}\r\n";

    // for Xan
    private String makeBizrate(Advert advert) {

        log.debug("makeBizrate");

        Map<Integer,String> vendorTable = getVendorTable(advert.getAdvertID());
        if(vendorTable.isEmpty()) { return "no Vendor Find"; }

        int count = 0;
        try(Connection cn = ds.getConnection()) {

            PreparedStatement ps;
            ResultSet rs;

            BufferedWriter of = new BufferedWriter(new FileWriter(PATH + advert.getFileName(), false));
            of.write(MessageFormat.format(HEADER_BIZRATE, advert.getDelim()));

            for(int subcatid : vendorTable.keySet()) {

                ps = cn.prepareStatement(SELECT_BIZRATE + vendorTable.get(subcatid));
                ps.setInt(1, subcatid);
                rs = ps.executeQuery();
                while(rs.next()) {
                    count++;

                    of.write(
                    MessageFormat.format(RESULT_BIZRATE, new Object[] {
/* 0*/               advert.getDelim(),
/* 1*/               rs.getString("sku"),
/* 2*/               rs.getString("itemName"),
/* 3*/               rs.getString("bizID"),
/* 4*/               "s",
/* 5*/               "180",
/* 6*/               rs.getString("qty"),
/* 7*/               "1",
/* 8*/               "1",
/* 9*/               rs.getDouble("sell_price"),
/*10*/               "3",
/*11*/               "3",
/*12*/               "US",
/*13*/               "2",
/*14*/               "no",
/*15*/               "none",
/*16*/               IMG_URL,
/*17*/               rs.getString("sku"),
/*18*/               rs.getString("mfrNumber")
                    }));

                }
                ps.close();
                rs.close();
            }
            of.close();
        }
        catch(IOException | SQLException e) {
            log.error("makeBizrate(): " + e);
        }

        return "" + count;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="makeNexttag(Advert advert)">

    final static String SELECT_NEXTTAG =
        "SELECT ad.mfrName mfrName,ta.mfrNumber mfrNumber,it.sell_price sell_price,LEFT(RIGHT(it.accwareCatID,2),2) accwareCode," +
        "it.name itemName,ta.conditions conditions,it.weight weight,IF(it.stock='Y',3,it.stock_quantity) qty,it.accwareCatID imageName " +
        "FROM advert.tempItemsGroupForAdvert ta " +
        "LEFT JOIN sklad.items it USING(item_id) " +
        "LEFT JOIN sklad.itemsAdvert ad USING(item_id)  " +
        "WHERE it.active='Y' AND it.category_id=? AND ad.nexttag=TRUE AND (it.sell_price > 50 OR it.vendor_id IN(2,83,27,29)) AND it.vendor_id IN ";

    final static String HEADER_NEXTTAG =
        "Price{0}Mfr Name{0}Mfr Number{0}yes{0}5-10{0}Item Name{0}URL{0}Conditions{0}IMG URL{0}SKU{0}Expedited shipping{0}Overnight shipping{0}Ground shipping{0}Quantity\r\n";
    final static String RESULT_NEXTTAG =
        "{1,number,#.00}{0}{2}{0}{3}{0}{4}{0}{5}{0}\"{6}\"{0}{7}{3}&advsite={8}&dtm={9}&sku={12}{0}{10}{0}{11}{12}.jpg{0}{13}{0}{14,number,#.00}{0}{15,number,#.00}{0}{16}{0}{17}\r\n";

    private String makeNexttag(Advert advert) {

        log.debug("makeNexttag");

        Map<Integer,String> vendorTable = getVendorTable(advert.getAdvertID());
        if(vendorTable.isEmpty()) { return "no Vendor Find"; }

        int count = 0;
        try(Connection cn = ds.getConnection()) {
            String condition;
            String oem;

            PreparedStatement ps;
            ResultSet rs;

            BufferedWriter of = new BufferedWriter(new FileWriter(PATH + advert.getFileName(), false));
            of.write(MessageFormat.format(HEADER_NEXTTAG, advert.getDelim()));

            for(int subcatid : vendorTable.keySet()) {

                ps = cn.prepareStatement(SELECT_NEXTTAG + vendorTable.get(subcatid));
                ps.setInt(1, subcatid);
                rs = ps.executeQuery();
                while(rs.next()) {
                    count++;

                    switch(rs.getInt("accwareCode")) {
                        case 0:
                        case 2:
                        case 4:
                        case 5:
                        case 8: condition = "New"; break;
                        default: condition = "Refurbished";
                    }
                    oem = "";
                    if(4 == rs.getInt("accwareCode")) {
                        oem = "OEM ";
                    }

                    of.write(
                        MessageFormat.format(RESULT_NEXTTAG, new Object[] {
/* 0*/                      advert.getDelim(),
/* 1*/                      rs.getDouble("sell_price"), // * 0.07 + rs.getDouble("sell_price"), // add 7%
/* 2*/                      rs.getString("mfrName"),
/* 3*/                      rs.getString("mfrNumber"),
/* 4*/                      "Yes",
/* 5*/                      "$5 - $20",
/* 6*/                      oem + rs.getString("itemName"),
/* 7*/                      SITE_URL,
/* 8*/                      advert.getName(),
/* 9*/                      currentTime,
/*10*/                      condition,
/*11*/                      IMG_URL,
/*12*/                      rs.getString("imageName"),
/*13*/                      rs.getString("imageName"),              // this accwareCatID
/*14*/                      10.0 + rs.getDouble("weight") * 0.80,   // expedited shipping
/*15*/                      30.0 + rs.getDouble("weight") * 0.95,   // overnight shipping
/*16*/                      "0.00",                                 // ground shipping
/*17*/                      rs.getInt("qty")
                        })
                    );

                }
                ps.close();
                rs.close();
            }
            of.close();
        }
        catch(IOException | SQLException e) {
            log.error("makeNexttag(): " + e);
        }

        return "" + count;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="makeBrokerBin(Advert advert)">
// vendor_id CVS=2 -> only Compuvest (itemVendor), AAA=29, ADV=27, CAS=83

    final static String SELECT_BROKERBIN =
        "SELECT it.item_id itemID,it.name itemName,ad.mfrName mfrName,ta.mfrNumber mfrNumber," +
        "it.hot_price hot_price,it.adv_price adv_price,ta.conditions conditions," +
        "IF(it.stock_quantity > 1,it.stock_quantity,1) stock_quantity " +
        "FROM advert.tempItemsGroupForAdvert ta " +
        "LEFT JOIN sklad.items it USING(item_id) " +
        "LEFT JOIN sklad.itemsAdvert ad USING(item_id)  " +
        "WHERE it.active='Y' AND it.category_id=? AND it.vendor_id IN(2,29,27,83) AND it.vendor_id IN ";

    final static String RESULT_BROKERBIN   =  "{1}{0}{2}{0}{3}{0}{4}{0}{5}{0}{6}\r\n";
    final static String RESULT_BROKERBIN_PW = "{1}{0}{2}{0}{3}{0}{4}{0}{5}{0}{6,number,#.00}\r\n";

    final static String HEADER_BROKERBIN_TL =
        //     1           2              3              4            5             6            3
        "part number{0}description{0}wholesale price{0}quantity{0}manufacturer{0}condition{0}retail price{0}\r\n";
    final static String RESULT_BROKERBIN_TL =
        "{1}{0}{2}{0}{3,number,#.00}{0}{4}{0}{5}{0}{3,number,#.00}\r\n";

    private String makeBrokerBin(Advert advert) {

        log.debug("makeBrokerBin");

        Map<Integer,String> vendorTable = getVendorTable(advert.getAdvertID());
        if(vendorTable.isEmpty()) { return "no Vendor Find"; }

        int count = 0;
        try(Connection cn = ds.getConnection()) {

            PreparedStatement ps;
            ResultSet rs;

            BufferedWriter of = new BufferedWriter(new FileWriter(PATH + advert.getFileName(), false));
            BufferedWriter psof = new BufferedWriter(new FileWriter(PATH + "interv-pw.txt", false));
            BufferedWriter trlp = new BufferedWriter(new FileWriter(PATH + "tradeloop.txt", false));

            trlp.write(MessageFormat.format(HEADER_BROKERBIN_TL, advert.getDelim()));

            for(int subcatid : vendorTable.keySet()) {

                ps = cn.prepareStatement(SELECT_BROKERBIN + vendorTable.get(subcatid));
                ps.setInt(1, subcatid);
                rs = ps.executeQuery();
                while(rs.next()) {
                    count++;

                    of.write(
                    MessageFormat.format(RESULT_BROKERBIN, new Object[] {
/* 0*/               advert.getDelim(),
/* 1*/               rs.getString("mfrNumber"),
/* 2*/               rs.getString("conditions"),
/* 3*/               rs.getString("adv_price").equals("Y") ? "Call" : rs.getString("hot_price"),
/* 4*/               rs.getString("stock_quantity"),
/* 5*/               rs.getString("mfrName"),
/* 6*/               rs.getString("itemName")
                    }));

                    psof.write(
                    MessageFormat.format(RESULT_BROKERBIN_PW, new Object[] {
/* 0*/               advert.getDelim(),
/* 1*/               rs.getString("mfrNumber"),
/* 2*/               rs.getString("mfrName"),
/* 3*/               rs.getString("itemName"),
/* 4*/               rs.getString("conditions"),
/* 5*/               rs.getString("stock_quantity"),
/* 6*/               rs.getDouble("hot_price")
                    }));

                    trlp.write(
                    MessageFormat.format(RESULT_BROKERBIN_TL, new Object[] {
/* 0*/               advert.getDelim(),
/* 1*/               rs.getString("mfrNumber"),
/* 2*/               rs.getString("itemName"),
/* 3*/               rs.getDouble("hot_price"),
/* 4*/               rs.getString("stock_quantity"),
/* 5*/               rs.getString("mfrName"),
/* 6*/               rs.getString("conditions")
                    }));

                }
                ps.close();
                rs.close();
            }
            of.close();
            psof.close();
            trlp.close();
        }
        catch(IOException | SQLException e) {
            log.error("makeBrokerBin(): " + e);
        }

        log.debug("sendEmail to BrokerBin");
        // send mail
        gmail.sendBinary(
            "webserver@compuvest.com",
            new String[] { "inventory@thebrokersite.com", "eddies@compuvest.com" },
            "Invetory from Compuvest.com " + new Date(),
            PATH + "brokerbin.txt"
        );

        return "" + count;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="makeBuyersEdge(Advert advert)">
//
    final static String SELECT_BUYERSEDGE =
        "SELECT it.name itemName,ad.mfrName mfrName,ta.mfrNumber mfrNumber,it.sell_price sell_price," +
        "ta.conditions conditions,ad.upc upc,it.accwareCatID accwareCode " +
        "FROM advert.tempItemsGroupForAdvert ta " +
        "LEFT JOIN sklad.items it USING(item_id) " +
        "LEFT JOIN sklad.itemsAdvert ad USING(item_id)  " +
        "WHERE it.active='Y' AND it.category_id=? AND it.vendor_id IN ";

    final static String HEADER_BUYERSEDGE =
        //        1          2                   3                 2                  4
        "Product Name{0}Model Number{0}Manufacturer Name{0}Manufacturer Part Number{0}UPC{0}" +
        //     5              6            7                           10                11
        "Selling Price{0}Availability{0}Referring{0}Product URL{0}Shipping Costs{0}Product Condition\r\n";

    final static String RESULT_BUYERSEDGE =
        "{1}{0}{2}{0}{3}{0}{2}{0}{4}{0}{5,number,#.00}{0}{6}{0}{0}{7}{2}&advsite={8}&dtm={9}&sku={12}{0}{10}{0}{11}\r\n";

    private String makeBuyersEdge(Advert advert) {

        log.debug("makeBuyersEdge");

        Map<Integer,String> vendorTable = getVendorTable(advert.getAdvertID());
        if(vendorTable.isEmpty()) { return "no Vendor Find"; }

        int count = 0;
        try(Connection cn = ds.getConnection()) {

            PreparedStatement ps;
            ResultSet rs;

            BufferedWriter of = new BufferedWriter(new FileWriter(PATH + advert.getFileName(), false));
            of.write(MessageFormat.format(HEADER_BUYERSEDGE, advert.getDelim()));

            for(int subcatid : vendorTable.keySet()) {

                ps = cn.prepareStatement(SELECT_BUYERSEDGE + vendorTable.get(subcatid));
                ps.setInt(1, subcatid);
                rs = ps.executeQuery();
                while(rs.next()) {
                    count++;

                    of.write(
                    MessageFormat.format(RESULT_BUYERSEDGE, new Object[] {
/* 0*/               advert.getDelim(),
/* 1*/               rs.getString("itemName"),
/* 2*/               rs.getString("mfrNumber"),
/* 3*/               rs.getString("mfrName"),
/* 4*/               rs.getString("upc"),
/* 5*/               rs.getDouble("sell_price"),
/* 6*/               "Yes",
/* 7*/               SITE_URL,
/* 8*/                advert.getName(),
/* 9*/               currentTime,
/*10*/               "See site",
/*11*/               rs.getString("conditions"),
/*12*/               rs.getString("accwareCode")
                    }));

                }
                ps.close();
                rs.close();
            }
            of.close();
        }
        catch(IOException | SQLException e) {
            log.error("makeBuyersEdge(): " + e);
        }

        return "" + count;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="makeCNET(Advert advert)">

    final static String SELECT_CNET =
        "SELECT ad.mfrName mfrName,ta.mfrNumber mfrNumber,it.sell_price sell_price," +
        "it.name itemName,ta.conditions conditions,(it.weight * 1.15) weight,it.accwareCatID accwareCode " +
        "FROM advert.tempItemsGroupForAdvert ta " +
        "LEFT JOIN sklad.items it USING(item_id) " +
        "LEFT JOIN sklad.itemsAdvert ad USING(item_id)  " +
        "WHERE it.active='Y' AND it.category_id=? AND ad.cnet=TRUE AND it.vendor_id IN ";

    final static String HEADER_CNET =
        // 1        2          3             4              5              6                          10
        "price{0}mfr_name{0}mfr_sku{0}merchandise type{0}in_stock{0}product_name{0}url{0}category{0}weight{0}delivery\r\n";

    final static String RESULT_CNET =
        "{1,number,#.00}{0}{2}{0}{3}{0}{0}{4}{0}\"{5}\"{0}{6}{0}{0}{7}{3}&advsite={8}&dtm={9}&sku={11}{0}{0}{10,number,#.00}{0}\r\n";

    private String makeCNET(Advert advert) {

        log.debug("makeCNET");

        Map<Integer,String> vendorTable = getVendorTable(advert.getAdvertID());
        if(vendorTable.isEmpty()) { return "no Vendor Find"; }

        int count = 0;
        try(Connection cn = ds.getConnection()) {

            PreparedStatement ps;
            ResultSet rs;

            BufferedWriter of = new BufferedWriter(new FileWriter(PATH + advert.getFileName(), false));
            of.write(MessageFormat.format(HEADER_CNET, advert.getDelim()));

            for(int subcatid : vendorTable.keySet()) {

                ps = cn.prepareStatement(SELECT_CNET + vendorTable.get(subcatid));
                ps.setInt(1, subcatid);
                rs = ps.executeQuery();
                while(rs.next()) {
                    count++;

                    of.write(
                    MessageFormat.format(RESULT_CNET, new Object[] {
/* 0*/               advert.getDelim(),
/* 1*/               rs.getDouble("sell_price"),
/* 2*/               rs.getString("mfrName"),
/* 3*/               rs.getString("mfrNumber"),
/* 4*/               "Yes",
/* 5*/               rs.getString("itemName"),
/* 6*/               (rs.getString("conditions").equals("Refurbished") || rs.getString("conditions").equals("Mnf. Refurbished"))  ? "R" : "",
/* 7*/               SITE_URL,
/* 8*/                advert.getName(),
/* 9*/               currentTime,
/*10*/               rs.getDouble("weight"),
/*11*/               rs.getString("accwareCode")
                    }));

                }
                ps.close();
                rs.close();
            }
            of.close();
        }
        catch(IOException | SQLException e) {
            log.error("makeCNET(): " + e);
        }

        return "" + count;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="makeApex()">

    final static String SELECT_APEX =
        "SELECT it.accwareCatID accwareCode,ad.mfrName mfrName,ta.mfrNumber mfrNumber,it.hot_price sell_price,it.name itemName," +
        "gc.name gcatName,sc.name catName,LEFT(RIGHT(it.accwareCatID,2),1) condID,it.weight weight,ad.upc upc,it.stock_quantity qnty " +
        "FROM advert.tempItemsGroupForAdvert ta " +
        "LEFT JOIN sklad.items it USING(item_id) " +
        "LEFT JOIN sklad.itemsAdvert ad USING(item_id)  " +
    	"LEFT JOIN sklad.category sc USING(category_id) " +
    	"LEFT JOIN sklad.category gc ON sc.parent=gc.category_id " +
        "WHERE it.active='Y' AND it.stock_quantity > 0";

    final static String HEADER_APEX =
        //    1        2   36452     7      1        8         9         10-2      6    11     12          13           14
        "description{0}id{0}link{0}price{0}title{0}brand{0}condition{0}img_link{0}mpn{0}upc{0}weight{0}product_type{0}quantity\r\n";

    final static String RESULT_APEX =
        "{1}{0}{2}{0}{3}{6}&advsite={4}&dtm={5}&sku={2}{0}{7,number,#.00}{0}{1}{0}{8}{0}{9}{0}{10}{2}.jpg{0}{6}{0}{11}{0}{12,number,0.00}{0}{13}{0}{14}\r\n";

    private String makeApex() {

        log.debug("makeApex");

        int count = 0;
        try(Connection cn = ds.getConnection()) {

            PreparedStatement ps;
	    ResultSet rs;

            BufferedWriter of = new BufferedWriter(new FileWriter(PATH + "Apex.csv", false));
            of.write(MessageFormat.format(HEADER_APEX, "\t"));

            ps = cn.prepareStatement(SELECT_APEX);
            rs = ps.executeQuery();
            while (rs.next()) {
                count++;

                String condition;
                switch(rs.getInt("condID")) {
                   // New,New Pulls/OEM
                   case 0:
                   case 6:
                   case 8:
                   case 9: condition = "New"; break;
                   // Mfr.Refurbished,Refurbished,Reconditioned
                   case 1:
                   case 3:
                   case 7: condition = "Refurbished"; break;
                   // Defective, Used, ***,Extra Life,Like New
                   case 2:
                   case 4:
                   case 5:condition = "Used"; break;
                   default: condition = "New";
                }

                of.write(
                MessageFormat.format(RESULT_APEX, new Object[] {
/* 0*/           "\t",
/* 1*/           rs.getString("itemName"),
/* 2*/           rs.getString("accwareCode"),
/* 3*/           SITE_URL,
/* 4*/           "apex",
/* 5*/           currentTime,
/* 6*/           rs.getString("mfrNumber"),
/* 7*/           rs.getDouble("sell_price"),
/* 8*/           rs.getString("mfrName"),
/* 9*/           condition,
/*10*/           IMG_URL,
/*11*/           rs.getString("upc"),
/*12*/           rs.getDouble("weight"),
/*13*/           rs.getString("gcatName") + " > " + rs.getString("catName"),
/*14*/           rs.getInt("qnty")
                }));
            }
            ps.close();
            rs.close();
            of.close();
        }
        catch(IOException | SQLException e) {
            log.error("makeApex(): " + e);
        }

        return "" + count;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="makePriceFallsCVS()">

    final static String RETURNPOLICY =
        "Compuvest.com must be informed of any problems and/or order discrepancies (wrong product received, wrong product ordered " +
        "or defective product) within 7 business days from receipt of product, for CPU within 3 days from receipt of product - fill out " +
        "RMA form and indicate in detail what the problem is, or email returns@compuvest.com." +
        "PLEASE NOTE: Compuvest.com require that shipping damage or damaged product was received from Compuvest is reported within 48 HOURS " +
        "of the product's delivery. Shipping carrier can take up to 2 weeks to resolve any damaged shipping claim." +
        "Unless otherwise indicated, all products may be returned within 30 days from the original invoice date for refund. " +
        "Return Merchandise Authorization (RMA) Number is required for all returns. A 15% restocking fee may apply as follows:" +
        "1. All refused packages will be accepted back in stock and credit will be processed within 5 (five) business days from receipt " +
        "of the merchandise less 15% restocking fee and shipping charges. No exceptions." +
        "2. The customer assumes full responsibility for product compatibility. Compuvest.com does not guarantee any product compatibility." +
        "3. Compuvest.com does not guarantee any cancellations, modifications, or alterations on any order if customer was not emailed " +
        "with confirmation from a Compuvest.com customer service representative." +
        "4. The purchaser agrees that if the purchase is cancelled after the close of business on the date that the credit card is charged, " +
        "and there is no fault by Compuvest, a fifteen percent (15%) fee may be charged at Compuvest's discretion.";

    final static String SELECT_PRICEFALLS =
        "SELECT it.accwareCatID accwareCode,LEFT(RIGHT(it.accwareCatID,2),2) cond,ad.mfrName mfrName,ta.mfrNumber mfrNumber,it.sell_price sell_price," +
        "it.name itemName,it.package_include packincl,gc.name gcatName,sc.name catName,it.weight weight,ad.upc upc,(it.stock_quantity - 2) qnty " +
        "FROM advert.tempItemsGroupForAdvert ta " +
        "LEFT JOIN sklad.items it USING(item_id) " +
        "LEFT JOIN sklad.itemsAdvert ad USING(item_id)  " +
    	"LEFT JOIN sklad.category sc USING(category_id) " +
    	"LEFT JOIN sklad.category gc ON sc.parent=gc.category_id " +
//        "WHERE it.stock_quantity > 3  AND it.sell_price > 5 AND it.vendor_id NOT IN(3,8,41,69) " + // TCH=3,IGM=8,DHD=41,SNX=69
        "WHERE it.active='Y' AND it.stock_quantity > 3  AND it.sell_price > 5 AND it.vendor_id IN(2) " + // CVS=2
        "LIMIT 20000";

    final static String HEADER_PRICEFALLS =
        //    1            29            2               3              4                 5                   6               7          8     9     10
        "Item Title*{0}Brand Name{0}Item Subtitle*{0}Category*{0}Price Ceiling* ($){0}Price Floor* ($){0}Fixed Price* ($){0}Quantity*{0}SKU{0}MPN{0}UPC{0}" +
        // 11              12                 13                14                15                                16
        "ISBN{0}\"Catch It\" Duration{0}Listing Duration*{0}Description{0}Condition* (NEW or USED){0}Returns Accepted?* (YES or NO){0}" +
        //           17                         18                                                      19
        "Return Policy Details{0}Items must be returned within (days){0}Refund will be given as (EXCHANGE, MONEY BACK, MERCHANDISE CREDIT){0}" +
        //           20                           21              22         23                       24
        "Shipping Location* (WW, USA, UK){0}Shipping Cost* ($){0}Tax{0}Payment Instructions{0}Gallery (YES or NO){0}" +
        //           25                            26                    27                   28-8
        "Home Page Featured (YES or NO){0}Bold Item (YES or NO){0}Highlight (YES or NO){0}Image 1 URL*{0}package\r\n";

    final static String RESULT_PRICEFALLS =
        "{1}{0}{29}{0}{2}{0}{3}{0}{4}{0}{5}{0}{6,number,#.00}{0}{7}{0}{8}{0}{9}{0}{10}{0}{11}{0}" +
        "{12}{0}{13}{0}{14}{0}{15}{0}{16}{0}{17}{0}{18}{0}{19}{0}{20}{0}{21}{0}{22}{0}{23}{0}" +
        "{24}{0}{25}{0}{26}{0}{27}{0}{28}{8}.jpg{0}{30}\r\n";

    private String makePriceFallsCVS() {

        log.debug("makePriceFallsCVS");

        int count = 0;
        try(Connection cn = ds.getConnection()) {
            String condition;

            PreparedStatement ps;
	    ResultSet rs;

            BufferedWriter of = new BufferedWriter(new FileWriter(PATH + "PriceFalls.txt", false));
            of.write(MessageFormat.format(HEADER_PRICEFALLS, "\t"));

            ps = cn.prepareStatement(SELECT_PRICEFALLS);
            rs = ps.executeQuery();
            while (rs.next()) {
                count++;

                switch(rs.getInt("cond")) {
                    case 0:
                    case 1:
                    case 2:
                    case 4:
                    case 5:
                    case 8: condition = "New"; break;
                    case 3:
                    case 7:
                    case 9:
                    case 10:
                    case 11:
                    case 12:
                    case 13:
                    case 14:
                    case 15:
                    case 30:
                    case 31:
                    case 32:
                    case 33:
                    case 58:
                    case 59:
                    case 71:
                    case 81:
                    case 82:
                    case 84:
                    case 90:
                    case 91:
                    case 92:
                    case 93: condition = "Refurbished"; break;
                    case 34:
                    case 40:
                    case 41:
                    case 42:
                    case 43:
                    case 44:
                    case 48:
                    case 49: condition = "Used"; break;
                    default: condition = "New";
                }

                of.write(
                MessageFormat.format(RESULT_PRICEFALLS, new Object[] {
/* 0*/           "\t",
/* 1*/           rs.getString("itemName"),
/* 2*/           "Compuvest.com",
/* 3*/           rs.getString("gcatName"), // + " > " + rs.getString("catName"),
/* 4*/           "",
/* 5*/           "",
/* 6*/           rs.getDouble("sell_price"),
/* 7*/           rs.getInt("qnty"), // quantity
/* 8*/           rs.getString("accwareCode"),
/* 9*/           rs.getString("mfrNumber"),
/*10*/           rs.getString("upc"),
/*11*/           "",
/*12*/           "0",
/*13*/           "7",
/*14*/           rs.getString("itemName"),
/*15*/           condition,
/*16*/           "YES",
/*17*/           RETURNPOLICY,
/*18*/           "7",
/*19*/           "MONEY BACK",
/*20*/           "USA",
/*21*/           "Free",
/*22*/           "",
/*23*/           "",
/*24*/           "",
/*25*/           "",
/*26*/           "",
/*27*/           "",
/*28*/           IMG_URL,
/*29*/           rs.getString("mfrName"),
/*30*/           rs.getString("packincl")
//* 3*/           SITE_URL,
//* 5*/           currentTime,
                }));
            }
            ps.close();
            rs.close();
            of.close();
        }
        catch(IOException | SQLException e) {
            log.error("makePriceFallsCVS(): " + e);
        }

        return "" + count;
    }

//
    final static String SELECT_PRICEFALLS_ALL =
        "SELECT it.accwareCatID accwareCode,LEFT(RIGHT(it.accwareCatID,2),2) cond,ad.mfrName mfrName,ta.mfrNumber mfrNumber,it.sell_price sell_price," +
        "it.name itemName,it.package_include packincl,gc.name gcatName,sc.name catName,it.weight weight,ad.upc upc,(it.stock_quantity - 2) qnty " +
        "FROM advert.tempItemsGroupForAdvert ta " +
        "LEFT JOIN sklad.items it USING(item_id) " +
        "LEFT JOIN sklad.itemsAdvert ad USING(item_id)  " +
    	"LEFT JOIN sklad.category sc USING(category_id) " +
    	"LEFT JOIN sklad.category gc ON sc.parent=gc.category_id " +
        "WHERE it.active='Y' AND it.stock_quantity > 3  AND it.sell_price > 5 AND it.vendor_id NOT IN(2) " + // CVS=2
        "LIMIT 50000";

    private String makePriceFallsALL() {

        log.debug("makePriceFallsALL");

        int count = 0;
        try(Connection cn = ds.getConnection()) {
            String condition;

            PreparedStatement ps;
	    ResultSet rs;

            BufferedWriter of = new BufferedWriter(new FileWriter(PATH + "PriceFallsALL.txt", false));
            of.write(MessageFormat.format(HEADER_PRICEFALLS, "\t"));

            ps = cn.prepareStatement(SELECT_PRICEFALLS_ALL);
            rs = ps.executeQuery();
            while (rs.next()) {
                count++;

                switch(rs.getInt("cond")) {
                    case 0:
                    case 1:
                    case 2:
                    case 4:
                    case 5:
                    case 8: condition = "New"; break;
                    case 3:
                    case 7:
                    case 9:
                    case 10:
                    case 11:
                    case 12:
                    case 13:
                    case 14:
                    case 15:
                    case 30:
                    case 31:
                    case 32:
                    case 33:
                    case 58:
                    case 59:
                    case 81:
                    case 82:
                    case 84:
                    case 90:
                    case 91:
                    case 92:
                    case 93: condition = "Refurbished"; break;
                    case 34:
                    case 40:
                    case 41:
                    case 42:
                    case 43:
                    case 44:
                    case 48:
                    case 49: condition = "Used"; break;
                    default: condition = "New";
                }

                of.write(
                MessageFormat.format(RESULT_PRICEFALLS, new Object[] {
/* 0*/           "\t",
/* 1*/           rs.getString("itemName"),
/* 2*/           "Compuvest.com",
/* 3*/           rs.getString("gcatName"), // + " > " + rs.getString("catName"),
/* 4*/           "",
/* 5*/           "",
/* 6*/           rs.getDouble("sell_price"),
/* 7*/           rs.getInt("qnty"), // quantity
/* 8*/           rs.getString("accwareCode"),
/* 9*/           rs.getString("mfrNumber"),
/*10*/           rs.getString("upc"),
/*11*/           "",
/*12*/           "0",
/*13*/           "7",
/*14*/           rs.getString("itemName"),
/*15*/           condition,
/*16*/           "YES",
/*17*/           RETURNPOLICY,
/*18*/           "7",
/*19*/           "MONEY BACK",
/*20*/           "USA",
/*21*/           "Free",
/*22*/           "",
/*23*/           "",
/*24*/           "",
/*25*/           "",
/*26*/           "",
/*27*/           "",
/*28*/           IMG_URL,
/*29*/           rs.getString("mfrName"),
/*30*/           rs.getString("packincl")
//* 3*/           SITE_URL,
//* 5*/           currentTime,
                }));
            }
            ps.close();
            rs.close();
            of.close();
        }
        catch(IOException | SQLException e) {
            log.error("makePriceFallsALL(): " + e);
        }

        return "" + count;
    }

// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="makeEpy()">

    final static String SELECT_EPY =
        "SELECT grp.name grpName,itvnd.vendor_id vendID,itvnd.alias als," +
        "grp.amazon_defQty0_30 amazondefQty0_30,grp.amazon_minQty0_30 amazonminQty0_30,grp.amazon_margin0_30 amazonmargin0_30,grp.amazon_charge0_30 amazoncharge0_30," +
        "grp.amzcomp_defQty0_30 amzcompdefQty0_30,grp.amzcomp_minQty0_30 amzcompminQty0_30,grp.amzcomp_margin0_30 amzcompmargin0_30,grp.amzcomp_charge0_30 amzcompcharge0_30," +
        "grp.ebay_defQty0_30 ebaydefQty0_30,grp.ebay_minQty0_30 ebayminQty0_30,grp.ebay_margin0_30 ebaymargin0_30,grp.ebay_charge0_30 ebaycharge0_30," +
        "grp.ebayali_defQty0_30 ebayalidefQty0_30,grp.ebayali_minQty0_30 ebayaliminQty0_30,grp.ebayali_margin0_30 ebayalimargin0_30,grp.ebayali_charge0_30 ebayalicharge0_30," +
        "grp.buycom_defQty0_30 buycomdefQty0_30,grp.buycom_minQty0_30 buycomminQty0_30,grp.buycom_margin0_30 buycommargin0_30,grp.buycom_charge0_30 buycomcharge0_30," +

        "grp.amazon_defQty31_60 amazondefQty31_60,grp.amazon_minQty31_60 amazonminQty31_60,grp.amazon_margin31_60 amazonmargin31_60,grp.amazon_charge31_60 amazoncharge31_60," +
        "grp.amzcomp_defQty31_60 amzcompdefQty31_60,grp.amzcomp_minQty31_60 amzcompminQty31_60,grp.amzcomp_margin31_60 amzcompmargin31_60,grp.amzcomp_charge31_60 amzcompcharge31_60," +
        "grp.ebay_defQty31_60 ebaydefQty31_60,grp.ebay_minQty31_60 ebayminQty31_60,grp.ebay_margin31_60 ebaymargin31_60,grp.ebay_charge31_60 ebaycharge31_60," +
        "grp.ebayali_defQty31_60 ebayalidefQty31_60,grp.ebayali_minQty31_60 ebayaliminQty31_60,grp.ebayali_margin31_60 ebayalimargin31_60,grp.ebayali_charge31_60 ebayalicharge31_60," +
        "grp.buycom_defQty31_60 buycomdefQty31_60,grp.buycom_minQty31_60 buycomminQty31_60,grp.buycom_margin31_60 buycommargin31_60,grp.buycom_charge31_60 buycomcharge31_60," +

        "grp.amazon_defQty61_90 amazondefQty61_90,grp.amazon_minQty61_90 amazonminQty61_90,grp.amazon_margin61_90 amazonmargin61_90,grp.amazon_charge61_90 amazoncharge61_90," +
        "grp.amzcomp_defQty61_90 amzcompdefQty61_90,grp.amzcomp_minQty61_90 amzcompminQty61_90,grp.amzcomp_margin61_90 amzcompmargin61_90,grp.amzcomp_charge61_90 amzcompcharge61_90," +
        "grp.ebay_defQty61_90 ebaydefQty61_90,grp.ebay_minQty61_90 ebayminQty61_90,grp.ebay_margin61_90 ebaymargin61_90,grp.ebay_charge61_90 ebaycharge61_90," +
        "grp.ebayali_defQty61_90 ebayalidefQty61_90,grp.ebayali_minQty61_90 ebayaliminQty61_90,grp.ebayali_margin61_90 ebayalimargin61_90,grp.ebayali_charge61_90 ebayalicharge61_90," +
        "grp.buycom_defQty61_90 buycomdefQty61_90,grp.buycom_minQty61_90 buycomminQty61_90,grp.buycom_margin61_90 buycommargin61_90,grp.buycom_charge61_90 buycomcharge61_90," +

        "grp.amazon_defQty91_180 amazondefQty91_180,grp.amazon_minQty91_180 amazonminQty91_180,grp.amazon_margin91_180 amazonmargin91_180,grp.amazon_charge91_180 amazoncharge91_180," +
        "grp.amzcomp_defQty91_180 amzcompdefQty91_180,grp.amzcomp_minQty91_180 amzcompminQty91_180,grp.amzcomp_margin91_180 amzcompmargin91_180,grp.amzcomp_charge91_180 amzcompcharge91_180," +
        "grp.ebay_defQty91_180 ebaydefQty91_180,grp.ebay_minQty91_180 ebayminQty91_180,grp.ebay_margin91_180 ebaymargin91_180,grp.ebay_charge91_180 ebaycharge91_180," +
        "grp.ebayali_defQty91_180 ebayalidefQty91_180,grp.ebayali_minQty91_180 ebayaliminQty91_180,grp.ebayali_margin91_180 ebayalimargin91_180,grp.ebayali_charge91_180 ebayalicharge91_180," +
        "grp.buycom_defQty91_180 buycomdefQty91_180,grp.buycom_minQty91_180 buycomminQty91_180,grp.buycom_margin91_180 buycommargin91_180,grp.buycom_charge91_180 buycomcharge91_180," +

        "grp.amazon_defQty181_240 amazondefQty181_240,grp.amazon_minQty181_240 amazonminQty181_240,grp.amazon_margin181_240 amazonmargin181_240,grp.amazon_charge181_240 amazoncharge181_240," +
        "grp.amzcomp_defQty181_240 amzcompdefQty181_240,grp.amzcomp_minQty181_240 amzcompminQty181_240,grp.amzcomp_margin181_240 amzcompmargin181_240,grp.amzcomp_charge181_240 amzcompcharge181_240," +
        "grp.ebay_defQty181_240 ebaydefQty181_240,grp.ebay_minQty181_240 ebayminQty181_240,grp.ebay_margin181_240 ebaymargin181_240,grp.ebay_charge181_240 ebaycharge181_240," +
        "grp.ebayali_defQty181_240 ebayalidefQty181_240,grp.ebayali_minQty181_240 ebayaliminQty181_240,grp.ebayali_margin181_240 ebayalimargin181_240,grp.ebayali_charge181_240 ebayalicharge181_240," +
        "grp.buycom_defQty181_240 buycomdefQty181_240,grp.buycom_minQty181_240 buycomminQty181_240,grp.buycom_margin181_240 buycommargin181_240,grp.buycom_charge181_240 buycomcharge181_240," +

        "grp.amazon_defQty241_more amazondefQty241_more,grp.amazon_minQty241_more amazonminQty241_more,grp.amazon_margin241_more amazonmargin241_more,grp.amazon_charge241_more amazoncharge241_more," +
        "grp.amzcomp_defQty241_more amzcompdefQty241_more,grp.amzcomp_minQty241_more amzcompminQty241_more,grp.amzcomp_margin241_more amzcompmargin241_more,grp.amzcomp_charge241_more amzcompcharge241_more," +
        "grp.ebay_defQty241_more ebaydefQty241_more,grp.ebay_minQty241_more ebayminQty241_more,grp.ebay_margin241_more ebaymargin241_more,grp.ebay_charge241_more ebaycharge241_more," +
        "grp.ebayali_defQty241_more ebayalidefQty241_more,grp.ebayali_minQty241_more ebayaliminQty241_more,grp.ebayali_margin241_more ebayalimargin241_more,grp.ebayali_charge241_more ebayalicharge241_more," +
        "grp.buycom_defQty241_more buycomdefQty241_more,grp.buycom_minQty241_more buycomminQty241_more,grp.buycom_margin241_more buycommargin241_more,grp.buycom_charge241_more buycomcharge241_more," +

        "itm.accwareCatID sku,itm.sell_price sellPrice,itm.hot_price hotPrice,IF(itm.stock='Y',1,0) stock,itm.stock_quantity qty,itm.weight weight," +

        "IFNULL(DATEDIFF(NOW(),itm.stockDate) BETWEEN 0 AND 30,1) old0_30," +
        "IFNULL(DATEDIFF(NOW(),itm.stockDate) BETWEEN 31 AND 60,0) old31_60," +
        "IFNULL(DATEDIFF(NOW(),itm.stockDate) BETWEEN 61 AND 90,0) old61_90," +
        "IFNULL(DATEDIFF(NOW(),itm.stockDate) BETWEEN 91 AND 180,0) old91_180," +
        "IFNULL(DATEDIFF(NOW(),itm.stockDate) BETWEEN 181 AND 240,0) old181_240," +
        "IFNULL(DATEDIFF(NOW(),itm.stockDate) >= 241,0) old241_more," +

        "vend.amazonMargin amazonMargin,vend.amazonCharge amazonCharge,vend.amazonQty amazonQty," +
        "vend.amzcompMargin amzcompMargin,vend.amzcompCharge amzcompCharge,vend.amzcompQty amzcompQty," +
        "vend.ebayMargin ebayMargin,vend.ebayCharge ebayCharge,vend.ebayQty ebayQty," +
        "vend.ebayAliMargin ebayAliMargin,vend.ebayAliCharge ebayAliCharge,vend.ebayAliQty ebayAliQty," +
        "vend.buycomMargin buycomMargin,vend.buycomCharge buycomCharge,vend.buycomQty buycomQty," +

        "vendcat.amazonSend amazonSend,vendcat.amzcompSend amzcompSend,vendcat.ebaySend ebaySend,vendcat.ebayAliSend ebayAliSend,vendcat.buycomSend buycomSend," +

        "eui.amazonPrice amazonSpecPrice,eui.amazonQty amazonSpecQty," +
        "eui.amzcompPrice amzcompSpecPrice,eui.amzcompQty amzcompSpecQty," +
        "eui.ebayPrice ebaySpecPrice,eui.ebayQty ebaySpecQty," +
        "eui.ebayAliPrice ebayAliSpecPrice,eui.ebayAliQty ebayAliSpecQty," +
        "eui.buycomPrice buycomSpecPrice,eui.buycomQty buycomSpecQty," +
        "eui.send euisend " +

        "FROM advert.epyGroups grp " +
        "LEFT JOIN advert.epyGroupSubCategory grpcat ON grpcat.group_id=grp.group_id " +
        "LEFT JOIN sklad.items itm USING(category_id) " +
        "LEFT JOIN advert.epyUserItems eui USING(item_id) " +
        "LEFT JOIN sklad.itemVendor itvnd USING(vendor_id) " +
        "LEFT JOIN advert.epyVendors vend USING(vendor_id) " +
        "LEFT JOIN advert.epyVendorSubCategories vendcat ON(vendcat.vendor_id=itm.vendor_id AND vendcat.category_id=itm.category_id) " +
        "WHERE itm.active='Y' AND grp.active=1 AND vend.active=1 ";

                                    //  1      2        3          4      5       6
    final static String HEADER     = "Group{0}SKU{0}Vendor Alias{0}Qty{0}Price{0}Weight\r\n";
    final static String RESULT_EPY = "{1}{0}{2}{0}{3}{0}{4,number,#}{0}{5,number,#.00}{0}{6}\r\n";

    private String makeEpy() {

        long time = System.currentTimeMillis();
        Map<Integer,Integer> amazonVendors =    new HashMap<>();
        Map<Integer,Integer> amzcompVendors =   new HashMap<>();
        Map<Integer,Integer> ebayVendors =      new HashMap<>();
        Map<Integer,Integer> ebayAliVendors =   new HashMap<>();
        Map<Integer,Integer> buycomVendors =    new HashMap<>();

        Integer count;
        int amazonCount = 0, amzcompCount = 0, ebayCount = 0, ebayAliCount = 0, buycomCount = 0, vendorID;
        int amazon_defQty, amazon_minQty;
        int amzcomp_defQty, amzcomp_minQty;
        int ebay_defQty, ebay_minQty;
        int ebayali_defQty, ebayali_minQty;
        int buycom_defQty, buycom_minQty;

        double sellPrice;
        double amazon_margin, amazon_charge;
        double amzcomp_margin, amzcomp_charge;
        double ebay_margin, ebay_charge;
        double ebayali_margin, ebayali_charge;
        double buycom_margin, buycom_charge;

        int stockQuantity;
        boolean stock;

        try(
            Connection cn = ds.getConnection();
            BufferedWriter amazon = new BufferedWriter(new FileWriter(PATH + "epy/amazon.txt", false));
            BufferedWriter amzcomp = new BufferedWriter(new FileWriter(PATH + "epy/amzcomp.txt", false));
            BufferedWriter ebay = new BufferedWriter(new FileWriter(PATH + "epy/ebay.txt", false));
            BufferedWriter ebayAli = new BufferedWriter(new FileWriter(PATH + "epy/ebayA.txt", false));
            BufferedWriter buycom = new BufferedWriter(new FileWriter(PATH + "epy/buycom.txt", false)) )
        {
            amazon.write(MessageFormat.format(HEADER, ","));
            amzcomp.write(MessageFormat.format(HEADER, ","));
            ebay.write(MessageFormat.format(HEADER, ","));
            ebayAli.write(MessageFormat.format(HEADER, ","));
            buycom.write(MessageFormat.format(HEADER, ","));

            PreparedStatement ps = cn.prepareStatement(SELECT_EPY); // + notIn);
            ResultSet rs = ps.executeQuery();
            while(rs.next()) {

                amazon_defQty = rs.getInt("amazondefQty0_30");
                amzcomp_defQty = rs.getInt("amzcompdefQty0_30");
                ebay_defQty = rs.getInt("ebaydefQty0_30");
                ebayali_defQty = rs.getInt("ebayalidefQty0_30");
                buycom_defQty = rs.getInt("buycomdefQty0_30");

                amazon_minQty = rs.getInt("amazonminQty0_30");
                amzcomp_minQty = rs.getInt("amzcompminQty0_30");
                ebay_minQty = rs.getInt("ebayminQty0_30");
                ebayali_minQty = rs.getInt("ebayaliminQty0_30");
                buycom_minQty = rs.getInt("buycomminQty0_30");

                amazon_margin = rs.getDouble("amazonmargin0_30");
                amzcomp_margin = rs.getDouble("amzcompmargin0_30");
                ebay_margin = rs.getDouble("ebaymargin0_30");
                ebayali_margin = rs.getDouble("ebayalimargin0_30");
                buycom_margin = rs.getDouble("buycommargin0_30");

                amazon_charge = rs.getDouble("amazoncharge0_30");
                amzcomp_charge = rs.getDouble("amzcompcharge0_30");
                ebay_charge = rs.getDouble("ebaycharge0_30");
                ebayali_charge = rs.getDouble("ebayalicharge0_30");
                buycom_charge = rs.getDouble("buycomcharge0_30");

                sellPrice = rs.getDouble("sellPrice");
                stockQuantity = rs.getInt("qty");
                vendorID = rs.getInt("vendID");
                stock = rs.getBoolean("stock");

                if(vendorID == 2) { // Compuvest
                    if(rs.getBoolean("old0_30")) {
                    }
                    else if(rs.getBoolean("old31_60")) {
                        amazon_defQty = rs.getInt("amazondefQty31_60");
                        amzcomp_defQty = rs.getInt("amzcompdefQty31_60");
                        ebay_defQty = rs.getInt("ebaydefQty31_60");
                        ebayali_defQty = rs.getInt("ebayalidefQty31_60");
                        buycom_defQty = rs.getInt("buycomdefQty31_60");

                        amazon_minQty = rs.getInt("amazonminQty31_60");
                        amzcomp_minQty = rs.getInt("amzcompminQty31_60");
                        ebay_minQty = rs.getInt("ebayminQty31_60");
                        ebayali_minQty = rs.getInt("ebayaliminQty31_60");
                        buycom_minQty = rs.getInt("buycomminQty31_60");

                        amazon_margin = rs.getDouble("amazonmargin31_60");
                        amzcomp_margin = rs.getDouble("amzcompmargin31_60");
                        ebay_margin = rs.getDouble("ebaymargin31_60");
                        ebayali_margin = rs.getDouble("ebayalimargin31_60");
                        buycom_margin = rs.getDouble("buycommargin31_60");

                        amazon_charge = rs.getDouble("amazoncharge31_60");
                        amzcomp_charge = rs.getDouble("amzcompcharge31_60");
                        ebay_charge = rs.getDouble("ebaycharge31_60");
                        ebayali_charge = rs.getDouble("ebayalicharge31_60");
                        buycom_charge = rs.getDouble("buycomcharge31_60");
                    }
                    else if(rs.getBoolean("old61_90")) {
                        amazon_defQty = rs.getInt("amazondefQty61_90");
                        amzcomp_defQty = rs.getInt("amzcompdefQty61_90");
                        ebay_defQty = rs.getInt("ebaydefQty61_90");
                        ebayali_defQty = rs.getInt("ebayalidefQty61_90");
                        buycom_defQty = rs.getInt("buycomdefQty61_90");

                        amazon_minQty = rs.getInt("amazonminQty61_90");
                        amzcomp_minQty = rs.getInt("amzcompminQty61_90");
                        ebay_minQty = rs.getInt("ebayminQty61_90");
                        ebayali_minQty = rs.getInt("ebayaliminQty61_90");
                        buycom_minQty = rs.getInt("buycomminQty61_90");

                        amazon_margin = rs.getDouble("amazonmargin61_90");
                        amzcomp_margin = rs.getDouble("amzcompmargin61_90");
                        ebay_margin = rs.getDouble("ebaymargin61_90");
                        ebayali_margin = rs.getDouble("ebayalimargin61_90");
                        buycom_margin = rs.getDouble("buycommargin61_90");

                        amazon_charge = rs.getDouble("amazoncharge61_90");
                        amzcomp_charge = rs.getDouble("amzcompcharge61_90");
                        ebay_charge = rs.getDouble("ebaycharge61_90");
                        ebayali_charge = rs.getDouble("ebayalicharge61_90");
                        buycom_charge = rs.getDouble("buycomcharge61_90");

                        sellPrice = rs.getDouble("hotPrice");
                    }
                    else if(rs.getBoolean("old91_180")) {
                        amazon_defQty = rs.getInt("amazondefQty91_180");
                        amzcomp_defQty = rs.getInt("amzcompdefQty91_180");
                        ebay_defQty = rs.getInt("ebaydefQty91_180");
                        ebayali_defQty = rs.getInt("ebayalidefQty91_180");
                        buycom_defQty = rs.getInt("buycomdefQty91_180");

                        amazon_minQty = rs.getInt("amazonminQty91_180");
                        amzcomp_minQty = rs.getInt("amzcompminQty91_180");
                        ebay_minQty = rs.getInt("ebayminQty91_180");
                        ebayali_minQty = rs.getInt("ebayaliminQty91_180");
                        buycom_minQty = rs.getInt("buycomminQty91_180");

                        amazon_margin = rs.getDouble("amazonmargin91_180");
                        amzcomp_margin = rs.getDouble("amzcompmargin91_180");
                        ebay_margin = rs.getDouble("ebaymargin91_180");
                        ebayali_margin = rs.getDouble("ebayalimargin91_180");
                        buycom_margin = rs.getDouble("buycommargin91_180");

                        amazon_charge = rs.getDouble("amazoncharge91_180");
                        amzcomp_charge = rs.getDouble("amzcompcharge91_180");
                        ebay_charge = rs.getDouble("ebaycharge91_180");
                        ebayali_charge = rs.getDouble("ebayalicharge91_180");
                        buycom_charge = rs.getDouble("buycomcharge91_180");

                        sellPrice = rs.getDouble("hotPrice");
                    }
                    else if(rs.getBoolean("old181_240")) {
                        amazon_defQty = rs.getInt("amazondefQty181_240");
                        amzcomp_defQty = rs.getInt("amzcompdefQty181_240");
                        ebay_defQty = rs.getInt("ebaydefQty181_240");
                        ebayali_defQty = rs.getInt("ebayalidefQty181_240");
                        buycom_defQty = rs.getInt("buycomdefQty181_240");

                        amazon_minQty = rs.getInt("amazonminQty181_240");
                        amzcomp_minQty = rs.getInt("amzcompminQty181_240");
                        ebay_minQty = rs.getInt("ebayminQty181_240");
                        ebayali_minQty = rs.getInt("ebayaliminQty181_240");
                        buycom_minQty = rs.getInt("buycomminQty181_240");

                        amazon_margin = rs.getDouble("amazonmargin181_240");
                        amzcomp_margin = rs.getDouble("amzcompmargin181_240");
                        ebay_margin = rs.getDouble("ebaymargin181_240");
                        ebayali_margin = rs.getDouble("ebayalimargin181_240");
                        buycom_margin = rs.getDouble("buycommargin181_240");

                        amazon_charge = rs.getDouble("amazoncharge181_240");
                        amzcomp_charge = rs.getDouble("amzcompcharge181_240");
                        ebay_charge = rs.getDouble("ebaycharge181_240");
                        ebayali_charge = rs.getDouble("ebayalicharge181_240");
                        buycom_charge = rs.getDouble("buycomcharge181_240");

                        sellPrice = rs.getDouble("hotPrice");
                    }
                    else if(rs.getBoolean("old241_more")) {
                        amazon_defQty = rs.getInt("amazondefQty241_more");
                        amzcomp_defQty = rs.getInt("amzcompdefQty241_more");
                        ebay_defQty = rs.getInt("ebaydefQty241_more");
                        ebayali_defQty = rs.getInt("ebayalidefQty241_more");
                        buycom_defQty = rs.getInt("buycomdefQty241_more");

                        amazon_minQty = rs.getInt("amazonminQty241_more");
                        amzcomp_minQty = rs.getInt("amzcompminQty241_more");
                        ebay_minQty = rs.getInt("ebayminQty241_more");
                        ebayali_minQty = rs.getInt("ebayaliminQty241_more");
                        buycom_minQty = rs.getInt("buycomminQty241_more");

                        amazon_margin = rs.getDouble("amazonmargin241_more");
                        amzcomp_margin = rs.getDouble("amzcompmargin241_more");
                        ebay_margin = rs.getDouble("ebaymargin241_more");
                        ebayali_margin = rs.getDouble("ebayalimargin241_more");
                        buycom_margin = rs.getDouble("buycommargin241_more");

                        amazon_charge = rs.getDouble("amazoncharge241_more");
                        amzcomp_charge = rs.getDouble("amzcompcharge241_more");
                        ebay_charge = rs.getDouble("ebaycharge241_more");
                        ebayali_charge = rs.getDouble("ebayalicharge241_more");
                        buycom_charge = rs.getDouble("buycomcharge241_more");

                        sellPrice = rs.getDouble("hotPrice");
                    }
                }

                // write to Amazon
                Map check = checkStatForWrite(
                    rs.getBoolean("euisend"),       // boolean purchasesend
                    rs.getDouble("amazonSpecPrice"),// double  purchaseprice
                    rs.getInt("amazonSpecQty"),     // int     purchaseqty
                    rs.getString("grpName"),        // String  groupname
                    rs.getBoolean("amazonSend"),    // boolean vendorsubcategorysend
                    rs.getDouble("amazonMargin"),   // double  vendormargin
                    rs.getDouble("amazonCharge"),   // double  vendorcharge
                    rs.getInt("amazonQty"),         // int     vendorqty
                    amazon_margin,                  // double  groupmargin
                    amazon_charge,                  // double  groupcharge
                    amazon_defQty,                  // int     groupdefqty
                    amazon_minQty,                  // int     groupminqty
                    stock,                          // boolean stock
                    rs.getInt("vendID"),            // int     vendorid
                    stockQuantity,                  // int     stockqty
                    sellPrice                       // double  sellprice
                );
                if((Boolean)check.get("use")) {
                    amazon.write(MessageFormat.format(RESULT_EPY, new Object[] {
                        ",",                    // 0
                        check.get("gname"),     // 1
                        rs.getString("sku"),    // 2
                        rs.getString("als"),    // 3
                        check.get("qty"),       // 4
                        check.get("price"),     // 5
                        rs.getString("weight")  // 6
                    }));

                    count = getCount(vendorID, amazonVendors);
                    amazonVendors.put(vendorID, count);
                }
                check.clear();

                // write to AmazonComp
                check = checkStatForWrite(
                    rs.getBoolean("euisend"),        // boolean purchasesend
                    rs.getDouble("amzcompSpecPrice"),// double  purchaseprice
                    rs.getInt("amzcompSpecQty"),     // int     purchaseqty
                    rs.getString("grpName"),         // String  groupname
                    rs.getBoolean("amzcompSend"),    // boolean vendorsubcategorysend
                    rs.getDouble("amzcompMargin"),   // double  vendormargin
                    rs.getDouble("amzcompCharge"),   // double  vendorcharge
                    rs.getInt("amzcompQty"),         // int     vendorqty
                    amzcomp_margin,                  // double  groupmargin
                    amzcomp_charge,                  // double  groupcharge
                    amzcomp_defQty,                  // int     groupdefqty
                    amzcomp_minQty,                  // int     groupminqty
                    stock,                           // boolean stock
                    rs.getInt("vendID"),             // int     vendorid
                    stockQuantity,                   // int     stockqty
                    sellPrice                        // double  sellprice
                );
                if((Boolean)check.get("use")) {
                    amzcomp.write(MessageFormat.format(RESULT_EPY, new Object[] {
                        ",",                    // 0
                        check.get("gname"),     // 1
                        rs.getString("sku"),    // 2
                        rs.getString("als"),    // 3
                        check.get("qty"),       // 4
                        check.get("price"),     // 5
                        rs.getString("weight")  // 6
                    }));

                    // count vendor items send to advert
                    count = getCount(vendorID, amzcompVendors);
                    amzcompVendors.put(vendorID, count);
                }
                check.clear();

                // write to Ebay
                check = checkStatForWrite(
                    rs.getBoolean("euisend"),     // boolean purchasesend
                    rs.getDouble("ebaySpecPrice"),// double  purchaseprice
                    rs.getInt("ebaySpecQty"),     // int     purchaseqty
                    rs.getString("grpName"),      // String  groupname
                    rs.getBoolean("ebaySend"),    // boolean vendorsubcategorysend
                    rs.getDouble("ebayMargin"),   // double  vendormargin
                    rs.getDouble("ebayCharge"),   // double  vendorcharge
                    rs.getInt("ebayQty"),         // int     vendorqty
                    ebay_margin,                  // double  groupmargin
                    ebay_charge,                  // double  groupcharge
                    ebay_defQty,                  // int     groupdefqty
                    ebay_minQty,                  // int     groupminqty
                    stock,                        // boolean stock
                    rs.getInt("vendID"),          // int     vendorid
                    stockQuantity,                // int     stockqty
                    sellPrice                     // double  sellprice
                );
                if((Boolean)check.get("use")) {
                    ebay.write(MessageFormat.format(RESULT_EPY, new Object[] {
                        ",",                    // 0
                        check.get("gname"),     // 1
                        rs.getString("sku"),    // 2
                        rs.getString("als"),    // 3
                        check.get("qty"),       // 4
                        check.get("price"),     // 5
                        rs.getString("weight")  // 6
                    }));

                    // count vendor items send to advert
                    count = getCount(vendorID, ebayVendors);
                    ebayVendors.put(vendorID, count);
                }
                check.clear();

                // write to EbayAly
                check = checkStatForWrite(
                    rs.getBoolean("euisend"),        // boolean purchasesend
                    rs.getDouble("ebayAliSpecPrice"),// double  purchaseprice
                    rs.getInt("ebayAliSpecQty"),     // int     purchaseqty
                    rs.getString("grpName"),         // String  groupname
                    rs.getBoolean("ebayAliSend"),    // boolean vendorsubcategorysend
                    rs.getDouble("ebayAliMargin"),   // double  vendormargin
                    rs.getDouble("ebayAliCharge"),   // double  vendorcharge
                    rs.getInt("ebayAliQty"),         // int     vendorqty
                    ebayali_margin,                  // double  groupmargin
                    ebayali_charge,                  // double  groupcharge
                    ebayali_defQty,                  // int     groupdefqty
                    ebayali_minQty,                  // int     groupminqty
                    stock,                           // boolean stock
                    rs.getInt("vendID"),             // int     vendorid
                    stockQuantity,                   // int     stockqty
                    sellPrice                        // double  sellprice
                );
                if((Boolean)check.get("use")) {
                    ebayAli.write(MessageFormat.format(RESULT_EPY, new Object[] {
                        ",",                    // 0
                        check.get("gname"),     // 1
                        rs.getString("sku"),    // 2
                        rs.getString("als"),    // 3
                        check.get("qty"),       // 4
                        check.get("price"),     // 5
                        rs.getString("weight")  // 6
                    }));

                    // count vendor items send to advert
                    count = getCount(vendorID, ebayAliVendors);
                    ebayAliVendors.put(vendorID, count);
                }
                check.clear();

                // write to Ebay
                check = checkStatForWrite(
                    rs.getBoolean("euisend"),       // boolean purchasesend
                    rs.getDouble("buycomSpecPrice"),// double  purchaseprice
                    rs.getInt("buycomSpecQty"),     // int     purchaseqty
                    rs.getString("grpName"),        // String  groupname
                    rs.getBoolean("buycomSend"),    // boolean vendorsubcategorysend
                    rs.getDouble("buycomMargin"),   // double  vendormargin
                    rs.getDouble("buycomCharge"),   // double  vendorcharge
                    rs.getInt("buycomQty"),         // int     vendorqty
                    buycom_margin,                  // double  groupmargin
                    buycom_charge,                  // double  groupcharge
                    buycom_defQty,                  // int     groupdefqty
                    buycom_minQty,                  // int     groupminqty
                    stock,                          // boolean stock
                    rs.getInt("vendID"),            // int     vendorid
                    stockQuantity,                  // int     stockqty
                    sellPrice                       // double  sellprice
                );
                if((Boolean)check.get("use")) {
                    buycom.write(MessageFormat.format(RESULT_EPY, new Object[] {
                        ",",                    // 0
                        check.get("gname"),     // 1
                        rs.getString("sku"),    // 2
                        rs.getString("als"),    // 3
                        check.get("qty"),       // 4
                        check.get("price"),     // 5
                        rs.getString("weight")  // 6
                    }));

                    // count vendor items send to advert
                    count = getCount(vendorID, buycomVendors);
                    buycomVendors.put(vendorID, count);
                }
            }
            ps.close();
            rs.close();

            // for amazon
            ps = cn.prepareStatement("UPDATE advert.epyVendors SET amazonSendQty=? WHERE vendor_id=?");
            for(int vendor : amazonVendors.keySet()) {
                amazonCount += amazonVendors.get(vendor);
                ps.setInt(1, amazonVendors.get(vendor));
                ps.setInt(2, vendor);
                ps.addBatch();
            }
            ps.executeBatch();
            ps.close();

            // for amzcomp
            ps = cn.prepareStatement("UPDATE advert.epyVendors SET amzcompSendQty=? WHERE vendor_id=?");
            for(int vendor : amzcompVendors.keySet()) {
                amzcompCount += amzcompVendors.get(vendor);
                ps.setInt(1, amzcompVendors.get(vendor));
                ps.setInt(2, vendor);
                ps.addBatch();
            }
            ps.executeBatch();
            ps.close();

            // for ebay
            ps = cn.prepareStatement("UPDATE advert.epyVendors SET ebaySendQty=? WHERE vendor_id=?");
            for(int vendor : ebayVendors.keySet()) {
                ebayCount += ebayVendors.get(vendor);
                ps.setInt(1, ebayVendors.get(vendor));
                ps.setInt(2, vendor);
                ps.addBatch();
            }
            ps.executeBatch();
            ps.close();

            // for ebayAli
            ps = cn.prepareStatement("UPDATE advert.epyVendors SET ebayAliSendQty=? WHERE vendor_id=?");
            for(int vendor : ebayAliVendors.keySet()) {
                ebayAliCount += ebayAliVendors.get(vendor);
                ps.setInt(1, ebayAliVendors.get(vendor));
                ps.setInt(2, vendor);
                ps.addBatch();
            }
            ps.executeBatch();
            ps.close();

            // for buycom
            ps = cn.prepareStatement("UPDATE advert.epyVendors SET buycomSendQty=? WHERE vendor_id=?");
            for(int vendor : buycomVendors.keySet()) {
                buycomCount += buycomVendors.get(vendor);
                ps.setInt(1, buycomVendors.get(vendor));
                ps.setInt(2, vendor);
                ps.addBatch();
            }
            ps.executeBatch();
            ps.close();

            // set status
            time = System.currentTimeMillis() - time;
            ps = cn.prepareStatement("UPDATE advert.stat SET cntAmazon=?,cntAmzComp=?,cntEbay=?,cntEbayAli=?,cntBuycom=?,time=?,build=NOW() WHERE name='Epy'");
            ps.setInt(1, amazonCount);
            ps.setInt(2, amzcompCount);
            ps.setInt(3, ebayCount);
            ps.setInt(4, ebayAliCount);
            ps.setInt(5, buycomCount);
            ps.setInt(6, (int)time);
	    ps.executeUpdate();
            ps.close();

        }
        catch(IOException | SQLException e) {
            log.error("makeEpy(): " + e);
        }

        return "\namazon:\t" + amazonCount + "\namzComp:\t" + amzcompCount + "\nebay:\t" + ebayCount + "\nebayA:\t" + ebayAliCount + "\nbuycom:\t" + buycomCount +  "\ntime:\t" + time + "ms.";
    }

    private static int getCount(int vendor, Map<Integer, Integer> vendorData) {

        return vendorData.containsKey(vendor) ? vendorData.get(vendor) + 1 : 1;
    }

    private Map checkStatForWrite(
        boolean purchasesend,
        double  purchaseprice,
        int     purchaseqty,
        String  groupname,
        boolean vendorsubcategorysend,
        double  vendormargin,
        double  vendorcharge,
        int     vendorqty,
        double  groupmargin,
        double  groupcharge,
        int     groupdefqty,
        int     groupminqty,
        boolean stock,
        int     vendorid,
        int     stockqty,
        double  sellprice
    ) {
        Map map = new HashMap();
        map.put("use", false);

        // check special fileds first
        if(purchasesend && vendorid == 2) {
            if(purchaseprice > 0d) { // check price field
                int qty = stock ? purchaseqty : (purchaseqty - stockqty > 0) ? stockqty : purchaseqty;
                if(qty > 0) { // and check quantity field
                    map.put("gname", "Compuvest Special");
                    map.put("price", purchaseprice);
                    map.put("qty", qty);
                    map.put("use", true);
                }
            }
        }
        // if dont have special check Vendor + Group filter
        if(!(Boolean)map.get("use") && vendorsubcategorysend) {
            // group
            double price = (sellprice / 100d * groupmargin) + sellprice + groupcharge;
            double qty = stock ? groupdefqty : stockqty - groupminqty;
            // vendor
            price = (price / 100d * vendormargin) + price + vendorcharge;
            qty = qty - vendorqty;
            if(qty > 0 && price > 0d) {
                map.put("gname", groupname);
                map.put("price", price);
                map.put("qty", qty);
                map.put("use", true);
            }
        }

        return map;
    }
// </editor-fold>


// <editor-fold defaultstate="collapsed" desc="sendEmail(String body)">

    private void sendEmail(String body) {

        gmail.send(
            "webserver@compuvest.com",
            new String[] {"dima@compuvest.com", "vagif@compuvest.com"},
            "Update advert file " + new Date(),
            body,
            "text/plain"
        );
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="sendPowerSourceFTPFile()">
// need lib/commons-net-1.4.1.jar
// info www.java2s.com/Code/Java/Network-Protocol/
//
    private String sendPowerSourceFTPFile() {

	String result = " Error";
        FTPClient ftp = null;
        try(InputStream input = new FileInputStream(PATH + "interv-pw.txt")) {
            ftp = new FTPClient();
            ftp.connect("ftp.powersourceonline.com");
            ftp.login("powersource", "powersource");

//	    System.out.println("Connect to . " + ftp.getReplyString());
            result = ftp.getReplyString();

            // passive mode because most of are behind firewalls
            ftp.enterLocalPassiveMode();

            ftp.storeFile("compuvest.txt", input);
        }
        catch(FTPConnectionClosedException e) {
            log.error("sendPowerSourceFTPFile(): " + e);
        }
        catch(Exception e) {
            log.error("sendPowerSourceFTPFile(): " + e);
        }
        finally {
            if(ftp != null) try { ftp.logout(); ftp.disconnect(); ftp = null; } catch(IOException ignore) { }
        }
        return result;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="sendLinksShareFTPFile()">
// need lib/commons-net-1.4.1.jar
// info www.java2s.com/Code/Java/Network-Protocol/
// http://www.informit.com/guides/content.aspx?g=java&seqNum=40
//Here are the ftp information

    private String sendLinksShareFTPFile() {

	String result = " Error";
        FTPClient ftp = null;
        try(InputStream input = new FileInputStream(PATH + "linkshare.txt")) {
            ftp = new FTPClient();
            ftp.connect("mftp.linksynergy.com");
            ftp.login("compuvest", "NqLbpfAG");

//	    System.out.println("Connect to . " + ftp.getReplyString());
            result = ftp.getReplyString();

            // passive mode because most of are behind firewalls
            ftp.enterLocalPassiveMode();

//            ftp.changeWorkingDirectory("1075741");
            ftp.storeFile("36385_nmerchandis" + new SimpleDateFormat("yyyyMMdd").format(new Date()) + ".txt", input);
        }
        catch(FTPConnectionClosedException e) {
            log.error("sendLinksShareFTPFile(): " + e);
        }
        catch(Exception e) {
            log.error("sendLinksShareFTPFile(): " + e);
        }
        finally {
            if(ftp != null) try { ftp.logout(); ftp.disconnect(); ftp = null; } catch(IOException ignore) { }
        }
        return result;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="sendBingFTPFile()">
// need lib/commons-net-1.4.1.jar
// info www.java2s.com/Code/Java/Network-Protocol/
// http://www.informit.com/guides/content.aspx?g=java&seqNum=40
//
    private String sendBingFTPFile() {

	String result = " Error";
        FTPClient ftp = null;
        try(InputStream input = new FileInputStream(PATH + "bing.txt")) {
            ftp = new FTPClient();
            ftp.connect("feeds.adcenter.microsoft.com");
            ftp.login("compuvest", "fX!GWh94T8");

//	    System.out.println("Connect to . " + ftp.getReplyString());
            result = ftp.getReplyString();

            // passive mode because most of are behind firewalls
            ftp.enterLocalPassiveMode();

//            ftp.changeWorkingDirectory("1075741");
            ftp.storeFile("cashback.txt", input);
        }
        catch(FTPConnectionClosedException e) {
            log.error("sendBingFTPFile()" + e);
        }
        catch(Exception e) {
            log.error("sendBingFTPFile(): " + e);
        }
        finally {
            if(ftp != null) try { ftp.logout(); ftp.disconnect(); ftp = null; } catch(IOException ignore) { }
        }
        return result;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="sendYahooFTPFile()">
// need lib/commons-net-1.4.1.jar
// info www.java2s.com/Code/Java/Network-Protocol/
// http://www.informit.com/guides/content.aspx?g=java&seqNum=40
//
    private String sendYahooFTPFile() {

	String result = " Error";
        FTPClient ftp = null;
        try(InputStream input = new FileInputStream(PATH + "yahoo.txt")) {
            ftp = new FTPClient();
            ftp.connect("ftp.productsubmit.adcentral.yahoo.com");
            ftp.login("compuvest2000", "965067065");

//	    System.out.println("Connect to . " + ftp.getReplyString());
            result = ftp.getReplyString();

            // passive mode because most of are behind firewalls
            ftp.enterLocalPassiveMode();

            ftp.changeWorkingDirectory("1075741");
            ftp.storeFile("data.txt", input);
        }
        catch(FTPConnectionClosedException e) {
            log("sendYahooFTPFile(): " + e);
        }
        catch(Exception e) {
            log("sendYahooFTPFile(): " + e);
        }
        finally {
            if(ftp != null) try { ftp.logout(); ftp.disconnect(); ftp = null; } catch(IOException ignore) { }
        }

        return result;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="sendShoppingFTPFile()">
// need lib/commons-net-1.4.1.jar
// info www.java2s.com/Code/Java/Network-Protocol/
//
    private String sendShoppingFTPFile() {

	String result = " Error";
    	FTPClient ftp = null;
        try(InputStream input = new FileInputStream(PATH + "shopping.txt")) {
            ftp = new FTPClient();
            ftp.connect("ftp.shopping.com");
            ftp.login("m9162", "KriA4maZ");

//	    System.out.println("Connect to . " + ftp.getReplyString());
            result = ftp.getReplyString();

            // passive mode because most of are behind firewalls
            ftp.enterLocalPassiveMode();

            ftp.storeFile("compuvest.txt", input);
        }
        catch(FTPConnectionClosedException e) {
            log.error("sendShoppingFTPFile(): " + e);
        }
        catch(Exception e) {
            log.error("sendShoppingFTPFile(): " + e);
        }
        finally {
            if(ftp != null) try { ftp.logout(); ftp.disconnect(); ftp = null; } catch(IOException ignore) { }
        }
        return result;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="sendOneWayFTPFile()">
//------------------------------------------------
//
// need lib/commons-net-1.4.1.jar
// info www.java2s.com/Code/Java/Network-Protocol/
//
    private String sendOneWayFTPFile() {

	String result = " Error";
    	FTPClient ftp = null;
        try(InputStream input = new FileInputStream(PATH + "oneway.txt")) {
            ftp = new FTPClient();
            ftp.connect("ftp.onewayshopping.com");
            ftp.login("compuvest@onewayshopping.com", "compvest#$");

//	    System.out.println("Connect to . " + ftp.getReplyString());
            result = ftp.getReplyString();

            // passive mode because most of are behind firewalls
            ftp.enterLocalPassiveMode();

            ftp.storeFile("oneway.txt", input);
        }
        catch(FTPConnectionClosedException e) {
            log.error("sendOneWayFTPFile(): " + e);
        }
        catch(Exception e) {
            log.error("sendOneWayFTPFile(): " + e);
        }
        finally {
            if(ftp != null) try { ftp.logout(); ftp.disconnect(); ftp = null; } catch(IOException ignore) { }
        }
        return result;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="HttpServlet methods.">
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        processRequest(request, response);
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        processRequest(request, response);
    }
    @Override
    public String getServletInfo() {
        return "AdvertFile";
    }

    @Override
    public void destroy() {
        ds = null;

        log.info("AdvertFile Destroy");
    }// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="class Advert">

    class Advert {

        private int id;
        private String name;
        private String fileName;
        private String delim;

        public Advert() { this(0, "", "", ","); }

        public Advert(int id, String name, String fileName, String delim) {
            this.id = id;
            this.name = name;
            this.fileName = fileName;
            this.delim = delim;
        }

        public int getAdvertID()        { return id; }
        public String getName()         { return name; }
        public String getFileName()     { return fileName; }
        public String getDelim()        { return delim; }
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="class Vendor">

    class Vendor {

        private int vendorid;
        private int subcatid;

        public Vendor() { this(0, 0); }

        public Vendor(int vendorid, int subcatid) {
            this.vendorid = vendorid;
            this.subcatid = subcatid;
        }

        public int getVendorID()    { return vendorid; }
        public int getSubcatID()    { return subcatid; }
    }
// </editor-fold>

}
